formula: counting presence

[Nastyd]

Hello!

I have to count presence of employees from sheets between START and END,
which is stored in G9 cell. I think it should be something like:
=SUM(IF(START:END!G9="present"; 1; 0)), but this one returns #REF and I
don't don't why.

 

[Don Guillett]

Try these from a post of mine today.

One way.
Put the sumif on each sheet with an indirect reference to d12 of the master.
then use
=sum(sheet1:sheet21!a2) where a2 in your sumif formula.

One way to put=SUMIF(B:B,Sheet1!D12) on each sheet is to select all>type the
formula in the cell desired>after the error msg>delete from the master and
use the sum in para 1.

OR
try this where you are summing sheet 2,3,4, & 5
=SUM(SUMIF(INDIRECT("Sheet"&{2,3,4,5}&"!A1:A17"),"a",INDIRECT("Sheet"&{2,3,4
,5}&"!B1:B17")))

 

[Nastyd]

One way.

Put the sumif on each sheet with an indirect reference to d12 of the
master.
then use
=sum(sheet1:sheet21!a2) where a2 in your sumif formula.
One way to put=SUMIF(B:B,Sheet1!D12) on each sheet is to select all>type
the
formula in the cell desired>after the error msg>delete from the master and
use the sum in para 1.

OR
try this where you are summing sheet 2,3,4, & 5
=SUM(SUMIF(INDIRECT("Sheet"&{2,3,4,5}&"!A1:A17"),"a",INDIRECT("Sheet"&{2,3,4
,5}&"!B1:B17")))

I thought about something like this, but this is very 'ugly'. Maybe there is
some better and nicer solution to do this in one, master formula?

 

[Don Guillett]

That's what the formula after the OR does

 

[Nastyd]

That's what the formula after the OR does

Yeah, I know, but this is just a template and problem is that those sheets
can have multiple names. This is why I have START and END. I need to sum
presence in every sheet, that is betweet those two. And there are about 30
employees, so the solution #1 would be very 'ugly'.

 

[Don Guillett]

Then I suggest a for/each macro to all with exceptions.