Countif & Sumif fx ref data in multiple columns

H

hal1011

I am using the following formulas which work fine.

=SUMIF(B2:B145,"AER*",I2:I145)

=COUNTIF(B2:B145,"AER*")

I would like to use similar formula to sum values in Column I, if Column A
contains “FPG†and Column B does not contain “AER*â€.

Also would like to use a similar formula to count values in Column B if
Column A contains “FPG†and Column B does not contain “AER*â€.

Can someone help?
 
L

Luke M

Summation scenario:
=SUMPRODUCT((A2:A145="FPG")*("B2:B145<>"AER*")*(I2:I145))

Counting scenario:
=SUMPRODUCT((A2:A145="FPG")*(B2:B145<>"AER*"))
 
H

hal1011

Thanks for the advice. I tried the formulas and the result is taking into
account the "FPG" part of the formulas, but not the "does not contain AER"
part of the formulas. Do you have any suggestions how to fix that?
 
L

Luke M

Change to:

Summation scenario:
=SUMPRODUCT((A2:A145="FPG")*(ISERROR(FIND("AER",B2:B145)))*(I2:I145))

Counting scenario:
=SUMPRODUCT((A2:A145="FPG")*(ISERROR(FIND("AER",B2:B145))))
 
H

hal1011

The results of these formulas are still only taking into account the first
set of critiera (FPG) and not the second. I am basically trying to apply the
same logic of a custom filter on the data in column B to include anything
that does not begin with AER. Any other advice?
 
H

hal1011

The results of these formulas are still only taking into account the first
set of critiera (FPG) and not the second. I am basically trying to apply the
same logic of a custom filter on the data in column B to include anything
that does not begin with AER. Any other advice?
 
D

Domenic

In that case, try the following instead...

=SUMPRODUCT(--(A2:A100="FPG"),--(LEFT(B2:B100,3)<>"AER"),I2:I100)

and

=SUMPRODUCT(--(A2:A100="FPG"),--(LEFT(B2:B100,3)<>"AER"))

Hope this helps!

http://www.xl-central.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top