calculating populated fields from multiple workbooks

[Kimmie40]

Hi
I have weekly reports and then a monthly report every month. On the
weekly report, engineers enter their change order numbers....on the
monthly report I want a formula that tells me how many change orders
they've had from each worksheet. So if the change orders are in C56,
C57, C58, C59, C60 on the weekly report, but each week the engineer
only had 2 each week. I want there to be a formula that says of the 5
cells from each of the 4 weekly reports, how many were populated with
a change order number.


Thanks
Kimmie40

 

[Pete_UK]

If the cells are blank if they do not contain a change order, then you
can just do:

=COUNT(week1!C56:C60)

or

=COUNTA(week1!C56:C60)

if they are not proper numbers. You can add these for the four weekly
reports to get a composite total, or if the sheets are physically
together in the workbook, you could try this:

=COUNT(week1:week4!C56:C60)

Hope this helps.

Pete

 

[Kimmie40]

If the cells are blank if they do not contain a change order, then you
can just do:

=COUNT(week1!C56:C60)

or

=COUNTA(week1!C56:C60)

if they are not proper numbers. You can add these for the four weekly
reports to get a composite total, or if the sheets are physically
together in the workbook, you could try this:

=COUNT(week1:week4!C56:C60)

Hope this helps.

Pete




- Show quoted text -

Wow....duh...thanks Pete. That was so easy its embarrassing.

 

[Pete_UK]

Sometimes the easy ones are the most elusive !!

Thanks for feeding back.

Pete

 

[Kimmie40]

Sometimes the easy ones are the most elusive !!

Thanks for feeding back.

Pete



- Show quoted text -

Pete, can you do the same for cells populated with text...not numbers?

 

[Pete_UK]

Try it using COUNTA rather than COUNT.

Excel is not very good with 3-D stuff, so if:

=COUNTA(week1:week4!C56:C60)

doesn't work you might have to try it like:

=COUNTA(week1!C56:C60) + COUNTA(week2!C56:C60) + COUNTA(week3!C56:C60)
+ COUNTA(week4!C56:C60)

Hope this helps.

Pete