nested countif function

[gavster]

I have an array of data:

dates in column A, reults for each position on test piece in coulumns d to AI

array covers rows 4 to 26

Currently I use a counif function to find all the zeros in the array:
=COUNTIF($D$20:$AI$26,0)

Now I'd like to be able to select the rows that are counted from on the basis of date. I have tried to use the Month and Max functions to select the last month from column A, but am failing to combine it with the Counif function properly:
=countif(and(month(MAX(A:A))),(a4:ai26,0))

Any ideas?

Thanks

[NBVC]

Countif only works with 1 criteria...you need to use Sumproduct() or if you have 2007, Countifs()...

I don't have 2007, so here is the Sumproduct solution....

=SUMPRODUCT((MONTH(A20:A26)=MAX(MONTH(A20:A26)))*(D20:AI26=0))

[gavster]

Thanks for that.

I'll give it a go.

Gavin.

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

[nattasiray]

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.

[daddylonglegs]

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

[NBVC]

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.

A couple of things...

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:

Please Login or Register  to view this content.

[gavster]

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.

[gavster]

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.

[NBVC]

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

Please Login or Register  to view this content.

where column AJ contains the word "Pass" if it is a pass.... change to reflect the actual string you're looking to count.

[gavster]

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.

[NBVC]

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.

Oh! I think I understand now....

try:

Please Login or Register  to view this content.

[gavster]

Thanks for that, a few alterations to get exactly what I wanted by I'm finally there.

For those interested:

=SUMPRODUCT((MONTH(A4:A26)=MONTH(MAX(A4:A26)))*(AJ4:AJ26<>"")*(AJ4:AJ26))

Thanks.
Gavin.