countif in multi sheet


G

ghost

Greeting,

I have the following table in 5 sheets (Summary, Jan, Feb, March & Aprial).

Sheet (Jan)
Employee ID Name No. of Absent
1 John 1
2 aaa 2
3 bbb 1

Sheet (Feb)

Employee ID Name No. of Absent
1 John 3
2 aaa 1
3 bbb 4
..
..
..
..
So on.

What I want to do is how to summarize the above table as follows by using
sum if or count if:
Sheet (summary)
Employee ID Name Absent Total
1 John 4
2 aaa 3
3 bbb 5
 
Ad

Advertisements

G

ghost

Hi Muddan,

It does not work and I do not know how it works, there is no indecators for
sheets!!!!
 
M

muddan madhu

suppose u have 5 sheets and named as, say summary,Jan,Feb,Mar,April

in summay sheet - Cell G2:G5 mention the sheet names. (eg.,
Jan,Feb,Mar....)

in all sheets data are in Col A, Col B, Col C I hope.
 
G

ghost

hi Muddan,

thank you , but would you please explain more for the other parts of the
formula
 
B

Bob Phillips

Try this alternative, no need for the values in G2:...

=SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(DATE(YEAR(TODAY()),{1;2;3;4;5;6;7;8;9;10;11;12},1),"mmm")&"'!B2:B4"),B2,
INDIRECT("'"&TEXT(DATE(YEAR(TODAY()),{1;2;3;4;5;6;7;8;9;10;11;12},1),"mmm")&"'!C2:C4")))
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top