SOLVED

# COUNTIFS Help Needed

Copper Contributor

# COUNTIFS Help Needed

Hi,

I am using the COUNTIFS formula below but running into #VALUE! error

=COUNTIFS('Raw Data'!\$A:\$A,'Composite by Relation'!\$A2,'Raw Data'!\$E:\$T,">0",'Raw Data'!\$D:\$D,C2)

Use Case:

I have 2 sheets,

Sheet1: Raw Data - Has about 424 rows that have information like Manager Name, Participant Name, Email Address, Relationship Type (Direct Report, Manager, Peer etc) for columns A to D and subsequent columns E to BF has a range of questions.

Sheet2: Composite By Relation - This is the sheet where I am trying to aggregate the information from Raw Data. In this sheet I have first 3 columns from Raw Data i.e., Manager Name, Email Address, Relationship Type and then 4 columns from E, F, G and H. The questions from Raw Data sheet are being grouped into 4 categories. For example, column E in Composite By Relation is looking at columns E through T in Raw Data sheet.

So, here is what I am trying to do...

In column E of Composite By Relation sheet, I am trying to match the Manager Name (A2) and Relationship Type (C2) in Raw Data's A and D columns and getting a count of all the occurrences across E to T in Raw Data is greater than 0.

The workaround is to have multiple COUNTIFS in a formula that adds each columns from E to T individually but that is too manual and cumbersome, so looking for easier alternatives. I have explored SUMPRODUCT, VLOOKUPs and COUNTIFS but seemed like COUNTIFS is the way to go about it but now I am stuck. I am open to any other ideas like using some other formula or builting a macro...whichever makes sense and minimizes the manual work.

4 Replies

# Re: COUNTIFS Help Needed

Best to switch to SUMPRODUCT:

``````=SUMPRODUCT(
('Raw Data'!\$A2:\$A100 = \$A2) * ('Raw Data'!\$E2:\$T100 > 0) *
('Raw Data'!\$D2:\$D100 = C2)
)``````

although note that this will entail losing the ability to reference entire columns (hence my choice of an upper row reference of 100).

To retain the option of using COUNTIFS - and so being able to reference entire columns with no detriment to calculation performance - would require a volatile set-up:

``````=SUM(
COUNTIFS(
'Raw Data'!\$A:\$A, \$A2,
OFFSET(
'Raw Data'!\$E:\$E,,SEQUENCE(, COLUMNS('Raw Data'!\$E:\$T), 0)
), ">0",
'Raw Data'!\$D:\$D, C2
)
)``````

As such, the benefits would be outweighed by the drawbacks, in my opinion.

Regards

# Re: COUNTIFS Help Needed

@JosWoolley Thanks so much for your quick response.

COUNTIFS is what I'd prefer as I expect the number of rows to grow over a period and SUMPRODUCT limitation is a non-starter. The formula with OFFSET works great for me. Appreciate your help!

Would the same formula work if I am trying to get an AVERAGE instead of COUNT? I tried the following by passing the correct arguments for AVERAGEIFS but doesn't seem to work

``````=SUM(
AVERAGEIFS('Raw Data'!\$E:\$T,
'Raw Data'!\$A:\$A, \$A2,
OFFSET(
'Raw Data'!\$E:\$E,,SEQUENCE(, COLUMNS('Raw Data'!\$E:\$T), 0)
), ">0",
'Raw Data'!\$D:\$D, \$D2
)
)``````

Is it because I have the column header which is a text included as part of the range and excel doesn't like it for some reason?

best response confirmed by abhinair (Copper Contributor)
Solution

# Re: COUNTIFS Help Needed

Obtaining the average using only the IFS family of functions would be more convoluted:

``````=LET(
α,'RawData'!\$A:\$A,
β,'RawData'!\$D:\$D,
γ,OFFSET('RawData'!\$E:\$E,,SEQUENCE(,COLUMNS('RawData'!\$E:\$T),0)),
SUM(SUMIFS(γ,γ,">0",α,\$A2,β,C2))/SUM(COUNTIFS(α,\$A2,γ,">0",β,C2))
)``````

I'd prefer:

``````=AVERAGE(
IF('RawData'!\$A2:\$A100=\$A2,
IF('RawData'!\$D2:\$D100=C2,
IF('RawData'!\$E2:\$T100>0,'RawData'!\$E2:\$T100)
)
)
)``````

keeping the end row reference to a suitably low upper bound.

Regards

# Re: COUNTIFS Help Needed

@JosWoolley That really helped. Thanks so much for your help on this one!