Use of COUNTIFS vs SUMIFS with Array Formula

[John_F]

I'm having trouble getting COUNTIFS to work with an array formula. I can get both the SUMIF and COUNTIF to work with a single criterion but when I add another criterion I can still get the SUMIFS to work but the COUNTIFS returns an error.

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	PRODUCT	DATE	QTY	UNIT PRICE	AMOUNT		PRODUCTS		QTY
2	PRODUCT A	01/19/14	490	4,957	2,428,930		PRODUCT A		4618	=SUMPRODUCT(--COUNTIF(G2:G3,A2:A31),C2:C31)
3	PRODUCT A	03/27/14	292	2,299	671,308		PRODUCT B		4618	=SUMPRODUCT(--SUMIF(A2:A31,G2:G3,C2:C31))
4	PRODUCT E	07/03/14	219	2,234	489,246
5	PRODUCT E	04/05/14	36	3,928	141,408		DATE
6	PRODUCT E	03/29/14	441	4,947	2,181,627		6/30/2014		#VALUE!	=SUMPRODUCT(--COUNTIFS(G2:G3,A2:A31,B2:B31,">"&G6),C2:C31)
7	PRODUCT C	07/13/14	397	3,159	1,254,123				589	=SUMPRODUCT(--SUMIFS(C2:C31,A2:A31,G2:G3,B2:B31,">"&G6))
8	PRODUCT D	12/18/14	350	3,571	1,249,850
9	PRODUCT E	07/10/14	453	1,235	559,455
10	PRODUCT E	05/19/14	46	3,544	163,024
11	PRODUCT B	01/16/14	465	4,664	2,168,760
12	PRODUCT B	03/27/14	436	3,254	1,418,744
13	PRODUCT C	01/06/14	366	4,281	1,566,846
14	PRODUCT D	05/11/14	184	2,274	418,416
15	PRODUCT A	03/22/14	447	3,705	1,656,135
16	PRODUCT D	03/27/14	7	1,096	7,672
17	PRODUCT E	04/13/14	497	1,443	717,171
18	PRODUCT A	05/04/14	470	4,807	2,259,290
19	PRODUCT B	02/21/14	209	2,375	496,375
20	PRODUCT A	03/19/14	99	1,070	105,930
21	PRODUCT E	10/25/14	457	3,314	1,514,498
22	PRODUCT B	12/28/14	291	2,553	742,923
23	PRODUCT C	04/05/14	439	2,413	1,059,307
24	PRODUCT B	12/30/14	139	2,234	310,526
25	PRODUCT B	01/23/14	395	2,624	1,036,480
26	PRODUCT A	02/26/14	249	4,709	1,172,541
27	PRODUCT E	09/09/14	465	1,282	596,130
28	PRODUCT A	01/23/14	477	4,240	2,022,480
29	PRODUCT E	12/21/14	248	3,586	889,328
30	PRODUCT B	12/18/14	159	2,102	334,218
31	PRODUCT C	11/17/14	383	3,854	1,476,082
COUNTIF vs SUMIF

 

[barry houdini]

In this version

=SUMPRODUCT(--COUNTIFS(G2:G3,A2:A31,B2:B31,">"&G6),C2:C31)

The COUNTIFS is invalid - the ranges marked in red need to be the same size in COUNTIFS, otherwise you get #VALUE! error

The COUNTIFS itself will work with the first two ranges swapped, i.e.

=COUNTIFS(A2:A31,G2:G3,B2:B31,">"&G6)

....but that returns a 2 element array so you can't use it in SUMPRODUCT with C2:C31

Your SUMIFS version works fine for your purposes, doesn't it? Why do you need this one, or is it for learning purposes?

You can make it work if you put the new criteria in as part of SUMPRODUCT rather than the COUNTIFS, like this:

=SUMPRODUCT(COUNTIF(G2:G3,A2:A31),--(B2:B31>G6),C2:C31)

BTW you don't require the -- in any of your formulas because the COUNTIFS/SUMIFS functions are returning numbers anyway

 

[John_F]

Thanks. I'm just trying to further understand the use of COUNTIFS in array formulas. Upon further review, I see that the SUMIFS version is returning a 2 element array but doesn't have to deal with C2:C31, so it works.
I didn't realize that COUNTIFS/SUMIFS would return numbers without some math operation but now that I think about it more, it makes sense that it does (return numbers). Thanks again for the help, you helped me out with another COUNTIFS question several weeks ago.