I believe this is a SUMPRODUCT issue

[WLMPilot]

I have a worksheet that list all bills (monthly and annual)

Column A = Owner of bill (B = Both, or initial of individual)
Column B = Frequency (M = Monthly, or 3 letter month for annual, ie JAN)
Column F = Payment

I wish to breakdown the list into categories and I have having trouble
figuring out how to look at two columns for criteria and sum off of column F
based on the criteria. I know how to do SUMIF, but don't think it can handle
what I need.

EXAMPLE in lay terms:
Sum F11:F100 if B11:B100 <> "M" and A11:A100 = "B"

The above is looking for the sum of all annual bills that are in Both names.

Your help is greatly appreciated!

Les

 

[Lars-Åke Aspelin]

I have a worksheet that list all bills (monthly and annual)

Column A = Owner of bill (B = Both, or initial of individual)
Column B = Frequency (M = Monthly, or 3 letter month for annual, ie JAN)
Column F = Payment

I wish to breakdown the list into categories and I have having trouble
figuring out how to look at two columns for criteria and sum off of column F
based on the criteria. I know how to do SUMIF, but don't think it can handle
what I need.

EXAMPLE in lay terms:
Sum F11:F100 if B11:B100 <> "M" and A11:A100 = "B"

The above is looking for the sum of all annual bills that are in Both names.

Your help is greatly appreciated!

Les

Try this formula

=SUMPRODUCT((B11:B100<>"M")*(A11:A100="B"),F11:F100)

Hope this helps / Lars-Åke

 

[WLMPilot]

Thanks! Works Great!

Les

Try this formula

=SUMPRODUCT((B11:B100<>"M")*(A11:A100="B"),F11:F100)

Hope this helps / Lars-Ã…ke