Excel Formula to SUMIFS first 3 characters are met

[ItalianPlatinum]

Hello - I have a current SUMIFS formula that falls down because the data in the large data set isn't truly empty (in AF).

=SUMIFS(M:M,C:C,R4,AF:AF,"<>")

Looking to enhance it so AF:AF will sum if the first three characters in AF is = to ABC or DEF.

Any help is greatly appreciated!

 

[mikerickson]

Does
=SUMIFS(M:M,C:C,R4,AF:AF,"?*") work for you in the all non-blank situation

"Looking to enhance it so AF:AF will sum if the first three characters in AF is = to ABC or DEF. "

try =SUMIFS(M:M,C:C,R4,AF:AF,"ABC")+SUMIFS(M:M,C:C,R4,AF:AF,"DEF")

 

[ItalianPlatinum]

well the actual data set has ABC123456789 or ABC11224455. not consistent but the first three characters always will be. either ABC or DEF. And no the first one did not work.

 

[CA_Punit]

Change the "ABC" to "ABC*" and "DEF" to "DEF*"

 

[ItalianPlatinum]

WORKS. Nice, thanks guys.