Back to Forms in Excel VBA archive index

Back to archive home

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

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.

=(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?

{=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

ie

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

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

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

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.

=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

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.

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

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.

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.