Forum Discussion

abhinair's avatar
abhinair
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 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!

 

excel
Formulas and Functions
  • JosWoolley's avatar
    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's avatar
    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's avatar
      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's avatar
        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

Resources