SUMIF with multiple criteria

[GBExcel via OfficeKB.com]

Hi,

I need help to SUMIF, subject to several criteria being met.

Here is the setup:

1. I need a formula for cell BB24. The header (cell BB23 contains the word;
Jan)
2. To the left of cell BB24 is BA24, which contains the phrase; Pay Slip.

3. Now we go to worksheet called; YEARNow. Column B9:B100 contains dates in
the format MMM DD, as in Jan 01, Feb 10, Mar 13, etc.
4. In the same worksheet is a column E9:E100, which contains either the word;
Business, or the word; Personal in each of its rows.
5. The values that need to be SUMIF'd are in the YEARnow worksheet in column
H9:H100

The formula in cell BB24 (See 1.), needs to evaluate the above and reflect
the value derived from a SUMIF of the YEARnow worksheet column H9:H100
according to the following criteria:

1. If BB24 contains the phrase, Pay Slip, (See 2.),
2. And if the YEARNow. Column B9:B100 contains months that match the cell
header BB23, which in this case is the month; Jan, (See 1.),
3. And if the YEARNow E9:E100, contains the word; Personal, (See 4.).

I've tried SUMIF in various formats, but keep getting a '0' anser when I
should be getting a value.

Appreciate the help.

GBExcel

 

[T. Valko]

Try this...

=IF(BA24="Pay
slip",SUMPRODUCT(--(TEXT(YEARNow!B9:B100,"mmm")=BB23),--(YEARNow!E9:E100="Personal"),YEARNow!H9:H100),"")

 

[GBExcel via OfficeKB.com]

Yes! Yes! Yes!

I'm so excited -- it really works!

Thank you. It was giving me a headache.

Can I ask a further question; What does the double minus sign, (as in --), in
the formula do?

GBExcel

Try this...

=IF(BA24="Pay
slip",SUMPRODUCT(--(TEXT(YEARNow!B9:B100,"mmm")=BB23),--(YEARNow!E9:E100="Personal"),YEARNow!H9:H100),"")

Hi,

[quoted text clipped - 32 lines]

GBExcel

 

[T. Valko]

What does the double minus sign, (as in --), in the formula do?

SUMPRODUCT multiplies arrays of numbers together then sums the results of
that muliplication to return the result. In the formula we've used some
logical tests and we have to convert those results into numeric values that
SUMPRODUCT can then process.

Here's how that happens...

These expressions will return an array of either TRUE or FALSE:

TEXT(YEARNow!B9:B100,"mmm")=BB23
YEARNow!E9:E100="Personal"

Let's assume this is a small sample of your data:

Per = Personal
Bus = Business

Jan 01...Per...10
Feb 11...Per...15
Jan 08...Bus...12
Mar 10...Per...10
Jan 03...Per...10

TEXT(cell_ref,"mmm") returns the short month name as a text string from a
date. So:

TEXT(Jan 01,"mmm") = Jan
TEXT(Feb 11,"mmm") = Feb
TEXT(Jan 08,"mmm") = Jan
TEXT(Mar 10,"mmm") = Mar
TEXT(Jan 03,"mmm") = Jan

We're testing to see if those month names = BB23 which holds the month name
Jan:

T = TRUE
F = FALSE

Jan = Jan = T
Feb = Jan = F
Jan = Jan = T
Mar = Jan = F
Jan = Jan = T

The double unary -- converts those logical values to either 1 or 0:

--TRUE = 1
--FALSE = 0

--(TEXT(YEARNow!B9:B100,"mmm")=BB23)

Now we have an array of 1s and 0s:

{1;0;1;0;1}

The same thing is done with:

YEARNow!E9:E100="Personal"

Per = Per = T
Per = Per = T
Bus = Per = F
Per = Per = T
Per = Per = T

--(YEARNow!E9:E100="Personal")

{1;1;0;1;1}

Now we have 3 arrays of numbers that can be multiplied together:

SUMPRODUCT({1;0;1;0;1},{1;1;0;1;1},{10;15;12;10;10})

Vertically:

1*1*10 = 10
0*1*15 = 0
1*0*12 = 0
0*1*10 = 0
1*1*10 = 10

Then the results of the multiplication are summed:

SUMPRODUCT({10;0;0;0;10}) = 20

So, based on the sample data:

=IF(BA24="Pay
slip",SUMPRODUCT(--(TEXT(YEARNow!B9:B100,"mmm")=BB23),--(YEARNow!E9:E100="Personal"),YEARNow!H9:H100),"")

=20

See this for more on SUMPRODUCT:

http://xldynamic.com/source/xld.SUMPRODUCT.html



exp101
--
Biff
Microsoft Excel MVP

Yes! Yes! Yes!

I'm so excited -- it really works!

Thank you. It was giving me a headache.

Can I ask a further question; What does the double minus sign, (as in --),
in
the formula do?

GBExcel

Try this...

=IF(BA24="Pay
slip",SUMPRODUCT(--(TEXT(YEARNow!B9:B100,"mmm")=BB23),--(YEARNow!E9:E100="Personal"),YEARNow!H9:H100),"")

Hi,

[quoted text clipped - 32 lines]

GBExcel

 

[GBExcel via OfficeKB.com]

Wow! I used to feel intelligent ..... ;-)

This may take a while for my mind to get around.

Thank you.

I appreciate the help.

GBExcel

What does the double minus sign, (as in --), in the formula do?

SUMPRODUCT multiplies arrays of numbers together then sums the results of
that muliplication to return the result. In the formula we've used some
logical tests and we have to convert those results into numeric values that
SUMPRODUCT can then process.

Here's how that happens...

These expressions will return an array of either TRUE or FALSE:

TEXT(YEARNow!B9:B100,"mmm")=BB23
YEARNow!E9:E100="Personal"

Let's assume this is a small sample of your data:

Per = Personal
Bus = Business

Jan 01...Per...10
Feb 11...Per...15
Jan 08...Bus...12
Mar 10...Per...10
Jan 03...Per...10

TEXT(cell_ref,"mmm") returns the short month name as a text string from a
date. So:

TEXT(Jan 01,"mmm") = Jan
TEXT(Feb 11,"mmm") = Feb
TEXT(Jan 08,"mmm") = Jan
TEXT(Mar 10,"mmm") = Mar
TEXT(Jan 03,"mmm") = Jan

We're testing to see if those month names = BB23 which holds the month name
Jan:

T = TRUE
F = FALSE

Jan = Jan = T
Feb = Jan = F
Jan = Jan = T
Mar = Jan = F
Jan = Jan = T

The double unary -- converts those logical values to either 1 or 0:

--TRUE = 1
--FALSE = 0

--(TEXT(YEARNow!B9:B100,"mmm")=BB23)

Now we have an array of 1s and 0s:

{1;0;1;0;1}

The same thing is done with:

YEARNow!E9:E100="Personal"

Per = Per = T
Per = Per = T
Bus = Per = F
Per = Per = T
Per = Per = T

--(YEARNow!E9:E100="Personal")

{1;1;0;1;1}

Now we have 3 arrays of numbers that can be multiplied together:

SUMPRODUCT({1;0;1;0;1},{1;1;0;1;1},{10;15;12;10;10})

Vertically:

1*1*10 = 10
0*1*15 = 0
1*0*12 = 0
0*1*10 = 0
1*1*10 = 10

Then the results of the multiplication are summed:

SUMPRODUCT({10;0;0;0;10}) = 20

So, based on the sample data:

=IF(BA24="Pay
slip",SUMPRODUCT(--(TEXT(YEARNow!B9:B100,"mmm")=BB23),--(YEARNow!E9:E100="Personal"),YEARNow!H9:H100),"")

=20

See this for more on SUMPRODUCT:

http://xldynamic.com/source/xld.SUMPRODUCT.html

exp101

Yes! Yes! Yes!

[quoted text clipped - 18 lines]

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Wow! I used to feel intelligent ..... ;-)

This may take a while for my mind to get around.

Thank you.

I appreciate the help.

GBExcel

What does the double minus sign, (as in --), in the formula do?

SUMPRODUCT multiplies arrays of numbers together then sums the results of
that muliplication to return the result. In the formula we've used some
logical tests and we have to convert those results into numeric values
that
SUMPRODUCT can then process.

Here's how that happens...

These expressions will return an array of either TRUE or FALSE:

TEXT(YEARNow!B9:B100,"mmm")=BB23
YEARNow!E9:E100="Personal"

Let's assume this is a small sample of your data:

Per = Personal
Bus = Business

Jan 01...Per...10
Feb 11...Per...15
Jan 08...Bus...12
Mar 10...Per...10
Jan 03...Per...10

TEXT(cell_ref,"mmm") returns the short month name as a text string from a
date. So:

TEXT(Jan 01,"mmm") = Jan
TEXT(Feb 11,"mmm") = Feb
TEXT(Jan 08,"mmm") = Jan
TEXT(Mar 10,"mmm") = Mar
TEXT(Jan 03,"mmm") = Jan

We're testing to see if those month names = BB23 which holds the month
name
Jan:

T = TRUE
F = FALSE

Jan = Jan = T
Feb = Jan = F
Jan = Jan = T
Mar = Jan = F
Jan = Jan = T

The double unary -- converts those logical values to either 1 or 0:

--TRUE = 1
--FALSE = 0

--(TEXT(YEARNow!B9:B100,"mmm")=BB23)

Now we have an array of 1s and 0s:

{1;0;1;0;1}

The same thing is done with:

YEARNow!E9:E100="Personal"

Per = Per = T
Per = Per = T
Bus = Per = F
Per = Per = T
Per = Per = T

--(YEARNow!E9:E100="Personal")

{1;1;0;1;1}

Now we have 3 arrays of numbers that can be multiplied together:

SUMPRODUCT({1;0;1;0;1},{1;1;0;1;1},{10;15;12;10;10})

Vertically:

1*1*10 = 10
0*1*15 = 0
1*0*12 = 0
0*1*10 = 0
1*1*10 = 10

Then the results of the multiplication are summed:

SUMPRODUCT({10;0;0;0;10}) = 20

So, based on the sample data:

=IF(BA24="Pay
slip",SUMPRODUCT(--(TEXT(YEARNow!B9:B100,"mmm")=BB23),--(YEARNow!E9:E100="Personal"),YEARNow!H9:H100),"")

=20

See this for more on SUMPRODUCT:

http://xldynamic.com/source/xld.SUMPRODUCT.html

exp101

Yes! Yes! Yes!

[quoted text clipped - 18 lines]

GBExcel