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 sumif
Name 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

Report •

#1
April 17, 2012 at 11:55:31
✔ Best Answer
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)

MIKE

http://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)

MIKE

http://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.3


Wild Cards:

? (question mark) Any single character

* (asterisk) Any number of characters

So using 4 question marks, were looking over 4
places and using at the 5th character.

Using 5 questions marks, were looking over 5
places and using the 6th character.

The asterisk at the end, just completes the string.

MIKE

http://www.skeptic.com/


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 •

Ask Question