Multiple Worksheet Calculation

  • Thread starter Thread starter Al9315
  • Start date Start date
A

Al9315

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)
 
I have tried =Countif(April:May!P5,"<10") but - error !?

Try this:

=INDEX(FREQUENCY(April:May!P5:Z5,9.9999999999),1)
 
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
 
=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
 
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
 
Back
Top