Hello
I have a stock system set up - WorksheetApril, WorksheetMay etc to March + a
final summary sheet.
I have tried =Countif(April:May!P5,"<10") but - error !?
=Countif(April!P5,"<10") - fine
=Countif(P5:Z5,"<10") - fine
I just wish to have this figure for each month in the summary sheet
Any help most welcome

Al

Hi,

COUNTIF doesn't support 3-D function references. Instead enter a countif in
each sheet in the same cell, for example D1 and then use

=SUM(April:May!D1)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Al9315" wrote:

Hello
I have a stock system set up - WorksheetApril, WorksheetMay etc to March + a
final summary sheet.
I have tried =Countif(April:May!P5,"<10") but - error !?
=Countif(April!P5,"<10") - fine
=Countif(P5:Z5,"<10") - fine
I just wish to have this figure for each month in the summary sheet
Any help most welcome

Al

Hi,

You can't use Countif in a 3D formula so you have choices

Use concatenated 1 dimensional countifs

=COUNTIF(April!$B$3,"<10")+COUNTIF(May!$B$3,"<10")

or load the excellent Morefunc addin and use

=COUNTIF.3D(April:May,<10)

http://xcell05.free.fr/morefunc/english/

Mike


"Al9315" wrote:

Hello
I have a stock system set up - WorksheetApril, WorksheetMay etc to March + a
final summary sheet.
I have tried =Countif(April:May!P5,"<10") but - error !?
=Countif(April!P5,"<10") - fine
=Countif(P5:Z5,"<10") - fine
I just wish to have this figure for each month in the summary sheet
Any help most welcome

Al

I have tried =Countif(April:May!P5,"<10") but - error !?

Try this:

=INDEX(FREQUENCY(April:May!P5:Z5,9.9999999999),1)

--
Biff
Microsoft Excel MVP


"Al9315" wrote in message
...
Hello
I have a stock system set up - WorksheetApril, WorksheetMay etc to March +
a
final summary sheet.
I have tried =Countif(April:May!P5,"<10") but - error !?
=Countif(April!P5,"<10") - fine
=Countif(P5:Z5,"<10") - fine
I just wish to have this figure for each month in the summary sheet
Any help most welcome

Al

Hi
Thanks for the great suggestion, some of the functions look most
interesting, however I just get an error when trying to download them

Al

Hi

Typed it in =INDEX(FREQUENCY(April:May!P5:Z5,9.9999999999),1)

Perfect - a bit beyond my understanding, but it works !!!!

Thank you so much !!!

Al

Hi,

You can also use this formula. I5:I7 holds the sheet tab names - April,
May, June.

=SUMPRODUCT(COUNTIF(INDIRECT(I5:I7&"!P5:Z5"),"<10" ))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Al9315" wrote in message
...
Hello
I have a stock system set up - WorksheetApril, WorksheetMay etc to March +
a
final summary sheet.
I have tried =Countif(April:May!P5,"<10") but - error !?
=Countif(April!P5,"<10") - fine
=Countif(P5:Z5,"<10") - fine
I just wish to have this figure for each month in the summary sheet
Any help most welcome

Al

=INDEX(FREQUENCY(April:May!P5:Z5,9.9999999999),1)

Here's how it works...

FREQUENCY can handle multiple area references and that includes 3d
references.

Basically, FREQUENCY performs a bunch of "count if's" based on the criteria
which are called bins. In this case we have a single bin, 9.9999999999.
FREQUENCY always calculates one more "count if" than the number of bins.
Since we have one bin we'll get back 2 results. If we had 5 bins we'd get
back 6 results.

The "count if's" are based on the values of the bins. The first "count if"
is always: Count if range is less than or equal to bin 1. Since you wanted
to count values less than 10 and the first "count if" does a less than or
equal to we need to make the bin a number less than 10. That's why I used
9.9999999999. It's as close to 10 as we can get and it meets the requirement
of being less than 10.

Ok, so we have our first result: "count if" range <=9.9999999999. Now, as I
said, FREQUENCY always retruns one more "count if" than the number of bins.
Since we have just a single bin and the first result is "count if"
<=9.9999999999 the next result will be "count if" 9.9999999999.

OK, we have our 2 results:

Count if <=9.9999999999 = 6
Count if 9.9999999999 = 3

These results are passed to the INDEX function:

=INDEX({6;3},1)

We want the first result: Count if <=9.9999999999. So we tell INDEX we want
the first result :

=INDEX({6;3},1) = 6

If you wanted the "count if" of values 9.9999999999 then we'd use:

=INDEX({6;3},2) = 3

To sum it up in plain English:

The count of values in the range April:May!P5:Z5 that are less than 10 is 6.



exp101
--
Biff
Microsoft Excel MVP


"Al9315" wrote in message
...
Hi

Typed it in =INDEX(FREQUENCY(April:May!P5:Z5,9.9999999999),1)

Perfect - a bit beyond my understanding, but it works !!!!

Thank you so much !!!

Al

Hi

Thank you very much for the detailed explanation, hugely appreciated !!!
Your help and ability to stimulate ones interest further is invaluable

Thank you again

Al

You're quite welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Al9315" wrote in message
...
Hi

Thank you very much for the detailed explanation, hugely appreciated !!!
Your help and ability to stimulate ones interest further is invaluable

Thank you again

Al