Week wise Sumif

[narendra1302]

Hi,

I have one sheet of excel, in which i have data of day wise number of incidents - Accidents, Hazards & House Keeping - Like following
A B C D E
Date Week Accidents Hazards HK
01/01/09 1 5 6 2
07/01/09 2 2 4 1
......
......

And in another sheet - I am having summary Weekwise
A B C D
Week Accidents Hazards HK
1 5 6 2
2 2 4 1

I have use SUMIF for sum the numbers of incidents in that week.

Now my problem is - when I used SUMIF for second column - for Hazard the number of Hazard get added in Accidents. So in above case accidents for week 1 will show 11, insted of 5 ?? Same case for HK...

thanks

Narendra

 

[sanrv1f]

try this

Excel Workbook
	A	B	C	D	E
1	Date	Week	Accidents	Hazards	HK
2	01/01/2009	1	5	6	2
3	07/01/2009	2	2	4	1
Sheet1

Excel 2003

Excel Workbook
	A	B	C	D
1	Week	Accidents	Hazards	HK
2	1	5	6	2
3	2	2	4	1
Sheet2

Excel 2003

Cell Formulas
Range	Formula
B2	=SUMPRODUCT((Sheet1!$B$2:$B$3=$A2)*(Sheet1!$C$1:$E$1=B$1),Sheet1!$C$2:$E$3)

 

[paulrit]

great

 

[narendra1302]

Thanks Sankar,

It is working fine.

Thanks

Narendra