COUNTIFS Help Needed

[abhinair]

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.

Thanks a lot in advance for your help!

[#2]

@abhinair 

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

[#3]

@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?

[#4 · Accepted solution]

@abhinair 

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

[#5]

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