COUNTIFS ARRAY VALUES

[LawNapier]

I am trying to build a formula where the below values are pulled from a table where......SUPPORT = A1 WINTEL = A2 CITRIX = A3. The only way I can get the formula to work is to list out the value string as indicated below in purple. If I ever want to change the value string, I would like to change it in the table and not in the formula. Anyone know how to modify my formula to reference the corresponding cell numbers in the table?

A
-------------
1 SUPPORT
2 WINTEL
3 CITRIX


=SUM(COUNTIFS(
'RAW DATA'!$E:$E,{"SUPPORT","WINTEL","CITRIX"},
'RAW DATA'!$K:$K,"INC",
'RAW DATA'!$M:$M,"JAN",
'RAW DATA'!$N:$N,"2019"))

 

[shaowu459]

=SUM(COUNTIFS(
'RAW DATA'!$E:$E,A1:A3,
'RAW DATA'!$K:$K,"INC",
'RAW DATA'!$M:$M,"JAN",
'RAW DATA'!$N:$N,"2019"))

Just replace the constant with range A1:A3, and press CTRL+SHIFT+ENTER for the array formula, no just press ENTER finish.

 

[DanteAmor]

Here are a couple of options for you to consider.

=SUM(COUNTIFS ('RAW DATA'!$E:$E,A1,
'RAW DATA'!$K:$K,"INC",
'RAW DATA'!$M:$M,"JAN",
'RAW DATA'!$N:$N,"2019"),
CONTAR.SI.CONJUNTO('RAW DATA'!$E:$E,A2,
'RAW DATA'!$K:$K,"INC",
'RAW DATA'!$M:$M,"JAN",
'RAW DATA'!$N:$N,"2019"),
CONTAR.SI.CONJUNTO('RAW DATA'!$E:$E,A3,
'RAW DATA'!$K:$K,"INC",
'RAW DATA'!$M:$M,"JAN",
'RAW DATA'!$N:$N,"2019"))


=SUMPRODUCT((((('RAW DATA'!$E2:$E10=A1)+('RAW DATA'!$E2:$E10=A2)+('RAW DATA'!$E2:$E10=A3))*
('RAW DATA'!$K2:$K10="INC")*
('RAW DATA'!$M2:$M10="JAN")*
('RAW DATA'!$N2:$N10=2019)*FILA(E2:E10))>0)+0)

 

[LawNapier]

=SUM(COUNTIFS(
'RAW DATA'!$E:$E,A1:A3,
'RAW DATA'!$K:$K,"INC",
'RAW DATA'!$M:$M,"JAN",
'RAW DATA'!$N:$N,"2019"))

Just replace the constant with range A1:A3, and press CTRL+SHIFT+ENTER for the array formula, no just press ENTER finish.

shaowu459 -- That doesn't work unfortunately, but thanks for the suggestion.

 

[LawNapier]

Here are a couple of options for you to consider.

=SUM(COUNTIFS ('RAW DATA'!$E:$E,A1,
'RAW DATA'!$K:$K,"INC",
'RAW DATA'!$M:$M,"JAN",
'RAW DATA'!$N:$N,"2019"),
CONTAR.SI.CONJUNTO('RAW DATA'!$E:$E,A2,
'RAW DATA'!$K:$K,"INC",
'RAW DATA'!$M:$M,"JAN",
'RAW DATA'!$N:$N,"2019"),
CONTAR.SI.CONJUNTO('RAW DATA'!$E:$E,A3,
'RAW DATA'!$K:$K,"INC",
'RAW DATA'!$M:$M,"JAN",
'RAW DATA'!$N:$N,"2019"))


=SUMPRODUCT((((('RAW DATA'!$E2:$E10=A1)+('RAW DATA'!$E2:$E10=A2)+('RAW DATA'!$E2:$E10=A3))*
('RAW DATA'!$K2:$K10="INC")*
('RAW DATA'!$M2:$M10="JAN")*
('RAW DATA'!$N2:$N10=2019)*FILA(E2:E10))>0)+0)

Dante - That does the trick. Thanks for your help!

 

[shaowu459]

Dante - That does the trick. Thanks for your help!

hi, the formula is right

Book1.xlsx
	A	B	C	D	E	F	G
1	A		A	Jan	1		3
2	B		B	Feb	2
3	C		C	Mar	3
4			D	Apr	4
5			E	Jan	5
6			F	Jun	6
7			A	Jan	7
8			B	Mar	8
9			C	Jan	9
Sheet3

Cell Formulas
Range	Formula
G1	G1	=SUM(COUNTIFS(C:C,A1:A3,D:D,"Jan"))
Press CTRL+SHIFT+ENTER to enter array formulas.

I checked your formula again, what goes wrong is you use "2019" in the formula, you should use 2019. "2019" will be recognised as a text. change your formula to:
=SUM(COUNTIFS(
'RAW DATA'!$E:$E,A1:A3,
'RAW DATA'!$K:$K,"INC",
'RAW DATA'!$M:$M,"JAN",
'RAW DATA'!$N:$N,2019))

CTRL SHIFT ENTER you will get expected result.
Refer to post #3, if you use "2019" instead of 2019 in the formula, the formula will not return expected result too.

 

[DanteAmor]

Glad we could help & thanks for the feedback