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.29Thank you in advance for your help!

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

Works perfectly. Thank you so much!!!

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!

Hi Mike, That didn't help either, the formula is picking up all the Z in there!

-Seasan

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.29When 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

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!!

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History