Countif & Sumif fx ref data in multiple columns

[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?

 

[Luke M]

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

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

 

[Domenic]

Try...

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

and

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

Hope this helps!

http://www.xl-central.com

 

[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?

 

[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))))

 

[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?

 

[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?

 

[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