MrExcel Publishing
Your One Stop for Excel Tips & Solutions

SUMPRODUCT/COUNTIF for date comparison


Posted by Mike Winters on December 04, 2001 1:43 PM

I have a column on my spreadsheet that contains dates. Each cell is conditionally formatted to show red if they are more than 1 yr old. What I want to do is have a formula (at the bottom of that column) that will count how many expired dates there are. The ranges I am trying to compute are AJ4:AJ110. Also, some cells are blank. Second problem: I need a formula to go through 3 columns (AL4:AN110), count only the cells that have a value of 90 or greater in all three cells of that row. I hope someone can help me.

Sincerely,

Mike Winters
United States Army

Posted by Aladin Akyurek on December 04, 2001 1:53 PM

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

Posted by Mike Winters on December 04, 2001 2:14 PM

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

Posted by Mike Winters on December 04, 2001 2:35 PM

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

Posted by Aladin Akyurek on December 04, 2001 2:43 PM

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


=======

Posted by Mike Winters on December 04, 2001 2:51 PM

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

Posted by IML on December 04, 2001 3:02 PM

Could you also get away with countif?

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

Posted by Mike Winters on December 04, 2001 3:06 PM

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

Posted by Aladin Akyurek on December 04, 2001 3:08 PM

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

Posted by Mike Winters on December 04, 2001 3:08 PM

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

Posted by IML on December 04, 2001 3:10 PM

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.

Posted by Aladin Akyurek on December 04, 2001 3:15 PM

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

========

Posted by Mike Winters on December 04, 2001 3:16 PM

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

Posted by Aladin Akyurek on December 04, 2001 3:28 PM

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

==========

Posted by Mike Winters on December 04, 2001 3:47 PM

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. ========== :


Posted by Aladin Akyurek on December 05, 2001 2:41 PM

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