Formula Help, SUMIFS

[Rob_010101]

Hello

Sheet 1

Sheet 2

In sheet 1, I need to know how many times each SMT has someone absent with 1 occasion, 2 occasions, 3 occasions and so on summed from sheet 2

So, using the above examples, Bob would show

In the above example sheet 2, I've collapsed some columns to protect private data.

Kind Regards
Chris

 

[Peter_SSs]

Live Stats

Thanks. Don't use 'Live Stats' in a formula that is on 'Live Stats' worksheet. It is not necessary and can lead to errors in some circumstances

Chris_010101.xlsm
	H								P
1
2	Bob								1
3	Bob								2
4	Dave								2
5	Bob								2
6
Occasions Current 6 Months

Chris_010101.xlsm
	R	S	T
1		1	2
2	Bob	1	2
3	Dave	0	1
Live Stats

Cell Formulas
Range	Formula
S2:T3	S2	=COUNTIFS('Occasions Current 6 Months'!$H:$H,$R2,'Occasions Current 6 Months'!$P:$P,S$1)

 

[Rob_010101]

Thanks. Don't use 'Live Stats' in a formula that is on 'Live Stats' worksheet. It is not necessary and can lead to errors in some circumstances

Chris_010101.xlsm
	H								P
1
2	Bob								1
3	Bob								2
4	Dave								2
5	Bob								2
6
Occasions Current 6 Months

Chris_010101.xlsm
	R	S	T
1		1	2
2	Bob	1	2
3	Dave	0	1
Live Stats

Cell Formulas
Range	Formula
S2:T3	S2	=COUNTIFS('Occasions Current 6 Months'!$H:$H,$R2,'Occasions Current 6 Months'!$P:$P,S$1)

Brilliant, thank you

With a bit of tweaking cell numbers, I got it there

=COUNTIFS('Occasions Current 6 Months'!$H:$H,$R3,'Occasions Current 6 Months'!$P:$P,S$2)

Many Thanks
Chris

 

[Peter_SSs]

You're welcome.

Have you tried XL2BB? It makes sample data much easier to deal with.

 

[Rob_010101]

You're welcome.

Have you tried XL2BB? It makes sample data much easier to deal with.

I've got it on my machine at home but my work won't allow it, unfortunately!

 

[Peter_SSs]

but my work won't allow it, unfortunately!

So if creating a thread at work, good idea in post #1 every time to state that you cannot use XL2BB due to work restrictions. It will save people asking all the time.