formula: counting presence

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. 


0
nastyd (3)
11/22/2004 7:51:37 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
429 Views

Similar Articles

[PageSpeed] 56

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")))


-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Nastyd" <nastyd@o2.fuckspam.pl> wrote in message
news:cntg0g$o9a$1@news.onet.pl...
> 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.
>
>


0
Don
11/22/2004 7:59:42 PM
> 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? 


0
nastyd (3)
11/22/2004 8:07:39 PM
That's what the formula after the OR does
> > 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")))
>


-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Nastyd" <nastyd@o2.fuckspam.pl> wrote in message
news:cntgul$fea$1@news.onet.pl...
> > 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?
>
>


0
Don
11/22/2004 11:39:41 PM
> 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'. 


0
nastyd (3)
11/23/2004 8:14:56 AM
Then I suggest a for/each macro to all with exceptions.

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Nastyd" <nastyd@o2.fuckspam.pl> wrote in message
news:cnuri1$52$1@news.onet.pl...
> > 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'.
>
>


0
Don
11/23/2004 1:24:26 PM
Reply:

Similar Artilces: