User Profile
abhinair
Copper Contributor
Joined 2 years ago
User Widgets
Recent Discussions
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!Solved1.8KViews0likes4Comments
Groups
Recent Blog Articles
No content to show