Countifs - multiple criteria over workbook - multiple pages

[rebadanson]

Hi all,

Total newbie here - trying to pick this up as fast as I am capable so forgive if this question could be better worded.

1 workbook
12 tabs (April - Mar)
4 Officer codes (initials)
78 Application types.

I am trying to figure out the countifs formula for counting a combination of criteria, but across all pages in the worksheet.

I need the total countifs, for each of 78 application types - per officer... Application type = STATS!, M2 and Officer type = STATS!, O2 , but for the life of me I can't figure out the instruction for the criteria range to be selected as the whole workbook - I can only figure out one page at a time - Apr!, May! etc.

Is anyone able to help me see what I'm missing please?

Many thanks and please be gentle - this is all new to me at present.

Reba

 

[etaf]

i think you need to use sumproduct() and put the sheets into a table

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&C1:C2&"'!a1:a40"),stats!M2,INDIRECT("'"&C1:C2&"'!b1:b40"),stats!m2))

but i dont know the range the data is in on each tab

this is just a sample

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&C1:C3&"'!a1:a40"),stats!M2,INDIRECT("'"&C1:C3&"'!b1:b40"),stats!O2))

its on drop box for a short while

3d count - ETAF.xlsx
	A	B	C	D	E	F	G
1			jan
2			feb			3
3			mar
4
5
Sheet1

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT(COUNTIFS(INDIRECT("'"&C1:C3&"'!a1:a40"),stats!M2,INDIRECT("'"&C1:C3&"'!b1:b40"),stats!O2))

3d count - ETAF.xlsx
	K	L	M	N	O	P	Q
1
2			1		a
3
4
stats

3d count - ETAF.xlsx
	A	B
1	officer
2	1	a
3	2
4	3
5	4
6	1	a
7	2
8	3
9	4
10	1
11	2
12	3
13	4
jan

3d count - ETAF.xlsx
	A	B
1	officer
2	1
3	1	a
4	2
5	2
6	2
7	1
feb

3d count - ETAF.xlsx

Shared with Dropbox

www.dropbox.com

 

[rebadanson]

Thank you so much for taking the time to reply - I am grateful to you

I'll have another look using your example.

Reba

 

[etaf]

you are welcome

with 365 version , you could also use vstack()
which is not volatile , as indirect is

Excel Functions Volatile Functions

Excel Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.

bettersolutions.com

A small number of the worksheet functions can be described as "volatile" functions.
This means that these functions constantly update when any changes are made to the worksheet.

I have added another simpler example for you - showing both vstack and indirect

count across multiple sheets - ETAF.xlsx
	A	B	C	D	E	F	G	H
1					Vstack			Indirect
2		A1	sheet1		3			3
3		A2	sheet2		3			3
4		A3	sheet3		1			1
5		A4	sheet4		0			0
6		A5	sheet5		2			2
7		A6			0			0
8		A7			0			0
9		A8			0			0
10					36
11
Sheet1

Cell Formulas
Range	Formula
H2:H9	H2	=SUMPRODUCT(COUNTIF(INDIRECT("'"&$C$2:$C$6&"'!$A$2:$A$6"),B2))
E2:E10	E2	=SUMPRODUCT(--(VSTACK(Sheet2:Sheet6!$A$2:$A$10)=B2))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B9	Expression	=SUMPRODUCT(COUNTIF(INDIRECT("'"&$C$2:$C$6&"'!$A$2:$A$6"),B2))	text	NO

count across multiple sheets - ETAF.xlsx
	A
1
2
3	a1
4	a1
5	a1
6
Sheet2

count across multiple sheets - ETAF.xlsx
	A
1
2	a2
3	a2
4
5
Sheet3

Dropbox

www.dropbox.com

 

[Georgiboy]

You can get away with just SUM instead of SUMPRODUCT for the VSTACK option i believe