Sum total using Sumproduct if two criteria met

K

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.
 
K

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.
 
S

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top