Sum total using Sumproduct if two criteria met

[KJ]

I'm using a sumproduct function to count how many times a combination of 2
criteria occur at the same time across a range.

Col A contains month and year and is custom formatted as mmm-yy eg Jan-08
Col B contains a numerical value eg £100 .

I’m using
=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*(B1:B100>0)) which counts how
many occurances of Jan-08 there are with a value of over 0.

I also need to know:

1. how to calculate the total of all the values in Col B when both my
criteria
are met. eg. sum of col B entries when Col A is Jan-08 and Col B has an
entry of any value in it. Any thoughts on how I amend the sumproduct
function to sum rather than count when two criteria are met.

2. Also, by using the >0 in my function, it then doesn't include any 0
entries I might have in Col B. I changed the formula to >=0 at the end as
follows
=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*(B1:B100>=0))
but it then included the cells that were blank too (I only want them to be
included in my count if I have a numerical value in the cell). Is there a
way of setting the 2nd criteria to be a value or entry of any numerical
value, even zero, perhaps by adding a wildcard*.

Thanks for any help you can offer.

 

[Ashish Mathur]

Hi,

Try this.

=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*isnumber(B1:B100))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[KJ]

Thanks - that sorts the problem of replacignthe zero to look for any
numerical value.

Any thoughts on the 1st point about calculating the sum total? Thx.

 

[Shane Devenshire]

Hi,

Question 1: Sum
=SUMPRODUCT(--(TEXT(A1:A100,"mmm-yy")="Jan-08"),--(B1:B100>0),B1:B100)

Question 2: Count >=
=SUMPRODUCT(--(TEXT(A1:A100,"mmm-yy")="Jan-08"),--(B1:B100>=0),--ISNUMBER(B1:B100))

 

[Shane Devenshire]

Hi Ashish,

I think this fails if B1 contains a negative number.

 

[Ashish Mathur]

Hi,

I wonder why - I am checking for a number.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com