LinkBack URL
About LinkBacks
Thanks for that.
I'll give it a go.
Gavin.
Thanks for that.
I'll give it a go.
Gavin.
Unfortunately that doesn't appear to work.
I know from doing it manually there are 26 zeros to be counted, but I'm getting a result of 90.
Just to clarify, there dates are in column A.
Data is in a block from D4 to AI26.
Gavin.
Please Attach the sample file contain data and the result that you want.
Sample data not to much and easily understand when grasp within 2 minutes.
N. Yauvasuta
Power User Excel.
Which dates are you trying to count, only the latest? In which case
=SUMPRODUCT((A4:A26=MAX(A4:A26))*( D4:AI26=0)*(D4:AI26<>""))
or any date in the latest month?
=SUMPRODUCT((A4:A26-DAY(A4:A26)=MAX(A4:A26)-DAY(MAX(A4:A26)))*( D4:AI26=0)*(D4:AI26<>""))
A couple of things...Originally Posted by gavster
Unfortunately that doesn't appear to work.
I know from doing it manually there are 26 zeros to be counted, but I'm getting a result of 90.
Just to clarify, there dates are in column A.
Data is in a block from D4 to AI26.
Gavin.
I messed up the order of Max and Month in the formula...should be reversed and also, as daddylonglegs formula suggests, the formula counts the blanks too, so to avoid any blanks, add another argument....
Try:
=SUMPRODUCT((MONTH(A4:A26)=MONTH(MAX(A4:A26)))*(D4:AI26=0)*(D4:AI26<>""))
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Sorry for not replying. Been a hectic weekend, and did'nt have a copy of the problem at home.
Any way, tried the new code and it works a treat.
Thanks very much.
Gavin.
Following on from this, I'm now trying to automate my monthly graph produc tion.
In the final column (AJ) of data are the number of passes at original test,
I'd now like to toatal these but again only for the last month.
Any ideas?
Gavin.
something like...Following on from this, I'm now trying to automate my monthly graph produc tion.
In the final column (AJ) of data are the number of passes at original test,
I'd now like to toatal these but again only for the last month.
Any ideas?
Gavin.
where column AJ contains the word "Pass" if it is a pass.... change to reflect the actual string you're looking to count.=SUMPRODUCT((MONTH(A4:A26)=MONTH(MAX(A4:A26)))*(AJ4:AJ26="Pass"))
Sorry, I didn't word (or type) that very well!
The last column (AJ) has a series of numbers in it, eg 31,30,31,29,32 etc...
each one relevant to a specific date.
By adapting the previous code you graciously supplied, I can count the number of entries, but I actually want the SUM of these figures.
Gavin.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks