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

On Wed, 13 Aug 2008 13:20:01 -0700, WLMPilot
<(E-Mail Removed)> 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:F100)

Hope this helps / Lars-Åke

Thanks! Works Great!

Les

"Lars-Ã…ke Aspelin" wrote:

> On Wed, 13 Aug 2008 13:20:01 -0700, WLMPilot
> <(E-Mail Removed)> 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:F100)
>
> Hope this helps / Lars-Ã…ke
>