sumif across multiple sheets in excel 2007 based on a condition

[dave@homedeliverygroup]

I have a small work book, tracks deliveries made and trucks used.
I have seven sheets Saturday thru Friday and one sheet that totals
everything up!

The weekly sheets have 34 rows representing 34 stores that we service and
the total sheet also has 34 rows that contain the totals for that week.

Here is my question I am trying to, on the totals sheet, sum up the total
trucks that we used per store per week. In the cell would be entered 0, 0.5,
1, 2 and so on depending on how many trucks we used for that day. Yes we use
0.5 if the store was combo'ed with another store. I need to have a count of
the cells that are greater than 0 or ">0" I don't need a sum just need to
know that we used a truck so I would need a count on the cells that are
greater then 0... Countif works on just one sheet and I can't find a solution
to this... if someone with more experience to just direct me in the right
direction...

 

[T. Valko]

I need to have a count of the cells

that are greater than 0 or ">0"

Try this...

=INDEX(FREQUENCY(Saturday:Friday!A1:A10,0),2)

 

[Max]

Biff,
Neat solution
Could you give a brief explanation as to what's happening in the FREQUENCY
bit in your:

=INDEX(FREQUENCY(Saturday:Friday!A1:A10,0),2)

Thanks

 

[T. Valko]

FREQUENCY returns an array of "count ifs" based on the bins argument. Let's
see how this works on the following data.

A1 = 0.5
A2 = 0
A3 = 0
A4 = 1
A5 = 2

We want a count of cells that are greater than 0.

=INDEX(FREQUENCY(A1:A5,0),2)

Returns 3

In this case we have just a single bin, 0. FREQUENCY will return two "count
ifs".

Count if A1:A5 is less than or equal to 0 = 2 (A2, A3)
Count if A1:A5 is greater than 0 = 3 (A1, A4, A5)

So, we have:

=INDEX({2;3},2)

We want the count of cells greater than 0 which is the second element of the
array of "count ifs" so we tell INDEX to return that 2nd element.

=INDEX({2;3},2)

Returns 3

So:

=INDEX(FREQUENCY(A1:A5,0),2)

=3

Of course, if the range to be counted is on a single sheet then a simple
COUNTIF(A1:A5,">0") will do. However, in the OP's application the range is
multiple sheets. FREQUENCY can handle references across multiple sheets, 3D
references and multiple area references (on the same sheet).

References across multiple sheets:

=INDEX(FREQUENCY(Saturday:Friday!A1:A10,0),2)

3D references:

=INDEX(FREQUENCY(Saturday:Friday!A1:C10,0),2)

Multiple area references:

=INDEX(FREQUENCY((A1:A10,B22:B25,X100:Z120),0),2)

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Count >0, multiple tabs
http://c0718892.cdn.cloudfiles.rackspacecloud.com/05_13_10.xlsx
Pdf preview:
http://c0718892.cdn.cloudfiles.rackspacecloud.com/05_13_10.pdf

 

[Max]

Thanks for the explanations, Biff

 

[T. Valko]

You're welcome, Max!

 

[dave@homedeliverygroup]

Thanks for the file Stanley and that worked perfect...



Sorry for the delay in responding... went on business trip...

and Biff thanks for the reply as well, although I could not follow exactly
what you had suggested...

Dave