SUMPRODUCT/COUNTIF for date comparison

Mike --

=SUMPRODUCT((AL4:AL110>=90)*(AM4:AM110>=90)*(AN4:AN110>=0))

will give the answer to your second question.

regarding your first question: What criterion do you apply to AJ4:AJ110 to determine expiry dates?

Aladin

For the conditional formatting, I use
=(TODAY()-AJ4)>365
to determine if it is over a year old, but the syntax of SUMPRODUCT and COUNTIF are still confusing me, so what seems like it should be simple I can't figure out. Hope that clarifies things.

Sincerely,

Mike Winters
United States Army

Hmmm. I wrote this formula:
{=COUNT(IF(AL4:AL110<60, IF(AM4:AM110<60, IF(AN4:AN110<60,1,0))))}
TO count the # of rows that have one or more cells (in one of three columns) with a value below 60. However, it is displaying way more than is accurate. I need it to only count a row if the cells are not blank, and only if one or more cell is below 60. If all three are below 60, it should still only count as one. Any ideas?

Sincerely,

Mike Winters
United States Army For the conditional formatting, I use

Then use:

=SUMPRODUCT((TODAY()-AJ4:AJ110>365)+0) to determine if it is over a year old, but the syntax of SUMPRODUCT and COUNTIF are still confusing me, so what seems like it should be simple I can't figure out. Hope that clarifies things.

COUNTIF and SUMIF are used to count and to sum when a single condition/criterion is involved.

In case of multiple conditions for both counting and summing you need SUMPRODUCT or array formulas.

Often you see SUMPRODUCT/array formulas built up with boolean terms like the ones I suggested to you. A * in such formulas is boolean AND. So:

=SUMPRODUCT((A1:A4="Blond")*(B1:B4="Tall"))

will count records/rows that contain "Blond" and "Tall" values.

How it works? It genereates first 2 arrays like:

{TRUE,FALSE,TRUE,TRUE}*{TRUE,TRUE,FALSE,TRUE}

and multiplying (ANDing) these two, it gets:

{1,0,0,1}

Multiplying logical values coerce Excel to treat TRUE as 1 and FALSE as 0.

Finally, the numbers in the last array are summed, producing a count.

Aladin

=======

How can I modify that formula you just gave me so that it won't count cells that are blank?

Sincerely,

Mike Winters
United States Army : For the conditional formatting, I use

Could you also get away with countif?

ie
=COUNTIF(AJ4:AJ110," < "&(NOW()-366))

Re: Could you also get away with countif?- Returned a 0

I suppose it is possible, but that formula returns a 0, so it's either not counting, or counting the wrong thing?

Sincerely,

Mike Winters
United States Army ie

This is very similar to your question with the >=90 condition. [ By the way, I has a small typo in the formula. It should be: =SUMPRODUCT((AL4:AL110>=90)*(AM4:AM110>=90)*(AN4:AN110>=90)) ]

The count question is similar: use either

=SUMPRODUCT((AL4:AL110 < 60)*(AM4:AM110 < 60)*(AN4:AN110 < 60))

or (the array-formula)

{=SUM((AL4:AL110 < 60)*(AM4:AM110 < 60)*(AN4:AN110 < 60))}

There is no really no need for using IFs in this situation.

PS. I'm assuming that the blank cells are really blank, that is, they do not contain formulas.

Aladin

Yes you can

I changed it to read
=COUNTIF(AJ4:AJ110,"<"&(TODAY()-366))
and it works, better yet, it doesnt count blank cells (or so it seems, the number is accurate). Thanks guys.

Sincerely,

Mike Winters
United States Army I suppose it is possible, but that formula returns a 0, so it's either not counting, or counting the wrong thing? : ie

Re: Yes you can

I think it was the added spaces between the quotes and the less than sign. Sometime things don't post properly to the board if those are included.

Just for the record...

=SUMPRODUCT((ISNUMBER(AJ4:AJ110))*(TODAY()-AJ4:AJ110>365)+0)

But, the COUNTIF formula Ian (IML) suggested is less expensive to use.

Aladin

========

Both of those formulas returned the same value (48), when the actual value is somewhere around 8. I think they are counting blank spaces, or maybe just counting all occurences of values less than 60. The data will be skewed if it counts more than one occurence of <60 per row.

Sincerely,

Mike Winters
United States Army

That means you have formulas that return blanks. So we need to expand the formula:

=SUMPRODUCT(ISNUMBER(AL4:AL110)*(AL4:AL110 < 60)*ISNUMBER(AM4:AM110)*(AM4:AM110 < 60)*ISNUMBER(AN4:AN110)*(AN4:AN110 < 60))

Gee, this sure looks ugly, but it should work.

Aladin

==========

Hmm. Thats a big formula. It returns a value of 1, and the correct value is 9. Hmmmm. I see what you did with this formula, and it looks good to my (inexperienced and unknowledgeable) eyes, but, as they say, the numbers never lie. Strange.

Sincerely,

Mike Winters
United States Army That means you have formulas that return blanks. So we need to expand the formula: =SUMPRODUCT(ISNUMBER(AL4:AL110)*(AL4:AL110 < 60)*ISNUMBER(AM4:AM110)*(AM4:AM110 < 60)*ISNUMBER(AN4:AN110)*(AN4:AN110 < 60)) Gee, this sure looks ugly, but it should work. ========== :

Counting Problem

Indeed. Since the question is to count records that span columns AL, AM, and AN, which contain at least one score that is lower than 60, an ORing formula is required. Moreover, blanks cells must not be included in the count. The formula that follows does just that:

=SUMPRODUCT((ISNUMBER(AL4:AL110))*(ISNUMBER(AM4:AM110))*(ISNUMBER(AN4:AN110))*(((AL4:AL110<60)+(AM4:AM110<60)+(AN4:AN110<60))={1,2,3}))

Aladin