Solved Nested Sumif and Mid functions

April 17, 2012 at 11:27:29
Specs: Windows XP
 SUMIF(MID(A9:A500,5,1),Z,C9:C500)Whats wrong in my formula above? I am trying to add columns Data 1-4 separately if the mid letter is Z. The name has 4 numbers in the front and 2 at the end Total sumif sumif sumif sumifName Data1 Data2 Data3 Data4 ####AAL## 4,300.75 5,569.46 (1,268.71) 8,107.53 ####ABR## 330.00 22.30 330.00 44.99####ZDB## 10,226.50 33.00 10,226.50 12.29 Thank you in advance for your help!

See More: Nested Sumif and Mid functions

#1
April 17, 2012 at 11:55:31
 The structure of a =SUMIF() is:=SUMIF(RangeOfThingsToBeExamined,CriteriaToBeMatched,RangeOfValuesToTotal)The structure of a =MID() is:=MID(OriginalText,PositionToStartPicking,NumberOfCharactersToPick)Can a =MID() statement be a Range?Try this:=SUMIF(A2:A4,"*Z*",C2:C4)MIKEhttp://www.skeptic.com/

Report •

#2
April 17, 2012 at 12:00:35
 Works perfectly. Thank you so much!!!

Report •

#3
April 17, 2012 at 13:21:20
 Hi Mike,Just another small problem...I wanted the sumif for 5th letter Z. For instance ####ZAL## and not ####BZZ##. Do you have any solution for this?Many thanks!

Report •

Related Solutions

#4
April 17, 2012 at 13:43:24
 Try:=SUMIF(A2:A4,"????Z*",C2:C4)MIKEhttp://www.skeptic.com/

Report •

#5
April 18, 2012 at 05:32:48
 Hi Mike,That didn't help either, the formula is picking up all the Z in there!-Seasan

Report •

#6
April 18, 2012 at 07:00:22
 Works, fine for me.With your data like:``` A B C D E 1) Name Data1 Data2 Data3 Data4 2) ####AAZ## 4,300.75 5,569.46 -1,268.71 8,107.53 3) ####AZR## 330 22.3 330 44.99 4) ####ZDB## 10,226.50 33 10,226.50 12.29 ```When I enter this formula in cell A6:NOTE: only 4 question marks.=SUMIF(A2:A4,"????Z*",C2:C4)I get just 33.When I enter this formula in cell A7:NOTE: 5 question marks.=SUMIF(A2:A4,"?????Z*",C2:C4)I get 22.3Wild Cards:? (question mark) Any single character* (asterisk) Any number of charactersSo using 4 question marks, were looking over 4places and using at the 5th character.Using 5 questions marks, were looking over 5places and using the 6th character.The asterisk at the end, just completes the string.MIKE

Report •

#7
April 18, 2012 at 07:26:29
 It works for me now and thank you for the explanation as well Mark! It helped me understand what I am doing better. I did a silly mistake of not adopting the same method on Non Z total so it kept giving me a difference. I fixed it with "?" as well as follows:=SUMIF(A2:A4,"<>?????Z*",C2:C4)Thank you very much for your prompt help!!

Report •