Forum Discussion

Copper Contributor

Apr 07, 2023

Solved

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...

excel

Formulas and Functions

JosWoolley

Apr 08, 2023

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

abhinair

Copper Contributor

Apr 07, 2023

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?

JosWoolley

Iron Contributor

Apr 08, 2023

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

abhinair
Copper Contributor
Apr 11, 2023
JosWoolley That really helped. Thanks so much for your help on this one!