User Profile
abhinair
Copper Contributor
Joined Apr 06, 2023
User Widgets
Recent Discussions
Re: COUNTIFS Help Needed
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?1.8KViews0likes2CommentsCOUNTIFS 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!Solved2.1KViews0likes4Comments
Recent Blog Articles
No content to show