sumif across multiple sheets in excel 2007 based on a condition
 

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...
--
thanks for your help

Dave

sumif across multiple sheets in excel 2007 based on a condition
 

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)

--
Biff
Microsoft Excel MVP


"dave@homedeliverygroup"
wrote in message ...
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...
--
thanks for your help

Dave

sumif across multiple sheets in excel 2007 based on a conditio
 

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
--
Max
Singapore
---

sumif across multiple sheets in excel 2007 based on a conditio
 

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)


--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
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
--
Max
Singapore
---

sumif across multiple sheets in excel 2007 based on a condition
 

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

sumif across multiple sheets in excel 2007 based on a conditio
 

Thanks for the explanations, Biff

sumif across multiple sheets in excel 2007 based on a conditio
 

You're welcome, Max!

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
Thanks for the explanations, Biff