CODEWITHSUNDEEP
excelexcel-formula
gfx2000
gfx2000

Reputation: 105

Merge two instances of IF + COUNTIF formulas?

I'm trying to count the team members involved in the project using Excel IF + COUNTIF formulas. So far I've been doing pretty well using this formula that checks if this role is in the list on the left, compares it to the right table, and how many people are assigned to this role:

=IF(H10<>"",COUNTIFS($P$9:$P$108,H10,$R$9:$R$108,"Onboard"),"")

=IF(H10<>"",COUNTIFS($P$9:$P$108,H10,$R$9:$R$108,"Onboard"),"")

But I would like to adjust it so that after doing so it would also add the specific manual number from the Q row. Again, only for the specific role that needs to be checked on the left. Here's what I came up with so far, but it only counts the times it matched:

=IF(H9<>"",COUNTIFS($P$9:$P$108,H9,$R$9:$R$108,"Onboard"),"")+IF(H9<>"",COUNTIFS($Q$9:$Q$108,">0",$P$9:$P$108,H9),"")

How can I modify this formula, so the exact number from the Q9 that matches the role on the left is added to the 'onboard' cell?

Upvotes: 0

Views: 471

Answers (1)

Kairu
Kairu

Reputation: 373

Instead of using COUNTIFS on the second part of the formula, use SUMIFS

=IF(H9<>"",COUNTIFS($P$9:$P$108,H9,$R$9:$R$108,"Onboard")+SUMIFS(Q9:$Q$108,$P$9:$P$108,H9,$R$9:$R$108,"Onboard"),"")

SUMIFS will take the exact numbers from the cells whereas COUNTIFS will count the number of occurrences based on the conditions

Also you can combine the COUNTIFS + SUMIFS inside the True statement of your IF function instead of checking the condition again

Upvotes: 0

Related Questions