K

#### KJ

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.