Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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


Check out our Excel Resources

Re: SUMPRODUCT/COUNTIF for date comparison

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


Re: SUMPRODUCT/COUNTIF for date comparison

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


Re: SUMPRODUCT/COUNTIF for date comparison

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


Re: SUMPRODUCT/COUNTIF for date comparison

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


=======


Re: SUMPRODUCT/COUNTIF for date comparison

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


Could you also get away with countif?

Posted by IML on December 04, 2001 3:02 PM
ie
=COUNTIF(AJ4:AJ110," < "&(NOW()-366))


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

Posted by Mike Winters on December 04, 2001 3:06 PM
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


Re: SUMPRODUCT/COUNTIF for date comparison

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


Yes you can

Posted by Mike Winters on December 04, 2001 3:08 PM
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

Posted by IML on December 04, 2001 3:10 PM
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...

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

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

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

Aladin

========


Re: SUMPRODUCT/COUNTIF for date comparison

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


Re: SUMPRODUCT/COUNTIF for date comparison

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

==========


Re: SUMPRODUCT/COUNTIF for date comparison

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



Counting Problem

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

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.