HELP PLEASE! How to do SUMIF if criteria is text and number? HI, I\'m trying to

Question

HELP PLEASE! How to do SUMIF if criteria is text and number?

HI,

I'm trying to do a SUMIF and the criteria is DOES NOT START WITH "B" or IS GREATER THAN "A1000" (either will work).

Basically, I don't want it to add any items that start with B OR don't add any item that is > A1000

How do I do this? Any help will be much appreciated.

Thanks!

ITEM DESCRIPTION VALUE A001 4 Bottles (different flavors) Vodka $48.00 B002 4 Bottles (different flavors) Vodka $48.00 B003 3 Bottles (blanco, reposado, anejo) Tequila $90.00 B004 2 Bottles White Hat Rum $40.00 B005 1 Bottle Crystal Creek Moonshine $22.00 A1001 Premium Party Package $248.00

Explanation / Answer

Hi,

If there are no empty cells in column A we can use this.

=SUMPRODUCT((LEFT(A2:A1000,1)="A")*(--MID(A2:A1000,2,99)<=1000)*(C2:C1000))

If column A does have empty cells then the formula becomes an ARRAY formula

=SUMPRODUCT((LEFT(A2:A1000,1)="A")*(IFERROR(--MID(A2:A1000,2,99),0)<=1000)*(C2:C1000))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.