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

JosWoolley

Iron Contributor

Apr 07, 2023

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

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!