Formula Help, SUMIFS

[Chris_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

 

[AlanY]

you can try the COUNTIFS function with name & occasions as criteria

 

[Chris_010101]

you can try the COUNTIFS function with name & occasions as criteria

I keep getting #VALUE! errors

 

[Peter_SSs]

I keep getting #VALUE! errors

With what formula?

 

[Chris_010101]

With what formula?

COUNTIFS

It's more than likely due to the way I am compiling it

 

[Peter_SSs]

COUNTIFS

That's not a formula, just a function name.

It's more than likely due to the way I am compiling it

Exactly, that's why it would help to show how you compiled it.

 

[Chris_010101]

That's not a formula, just a function name.

Sorry,

=COUNTIFS('Occasions Current 6 Months'!H:H,@ 'Occasions Current 6 Months'!P:P, 'Live Stats'!R3, 'Live Stats'!S2)

 

[Peter_SSs]

=COUNTIFS('Occasions Current 6 Months'!H:H,@ 'Occasions Current 6 Months'!P:P, 'Live Stats'!R3, 'Live Stats'!S2)

Thanks. What is the name of the worksheet this formula is on?

 

[Chris_010101]

Thanks. What is the name of the worksheet this formula is on?

Live Stats

 

[AlanY]

with sample data

Book1
	A	B	C	D	E	F	G
1		1	2	3	4	5
2	Name1	2	0	1	0	0
3	Name2	0	0	2	0	0
4	Name3	1	2	0	0	1
5
6	Name1	1
7	Name2	3
8	Name1	1
9	Name2	3
10	Name1	3
11	Name3	2
12	Name3	2
13	Name3	1
14	Name3	5
Sheet1

Cell Formulas
Range	Formula
B2:F4	B2	=COUNTIFS($A$6:$A$14,$A2,$B$6:$B$14,B$1)