Apr 06 2023 10:39 PM
Apr 06 2023 10:39 PM
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)
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!
Apr 06 2023 11:07 PM
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.
Apr 07 2023 12:35 AM - edited Apr 07 2023 12:42 AM
@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?
Apr 07 2023 11:29 PMSolution
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)) )
=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.