I believe this is a SUMPRODUCT issue
 

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

I believe this is a SUMPRODUCT issue
 

On Wed, 13 Aug 2008 13:20:01 -0700, WLMPilot
wrote:

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:F10 0)

Hope this helps / Lars-Åke

I believe this is a SUMPRODUCT issue
 

Thanks! Works Great!

Les

"Lars-Ã…ke Aspelin" wrote:

On Wed, 13 Aug 2008 13:20:01 -0700, WLMPilot
wrote:

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:F10 0)

Hope this helps / Lars-Ã…ke