'Is there a better way to Sumif?
I have 18 different ranges on 1 sheet that have names and totals in them. Each range has a list of names with a quantity next to it and a total at the bottom. I can't just search the complete sheet due to the fact that if the same name appears next to every quantity in one range that name also appears next to the total for that range. I need to look up the names and sum the quantities of just the ranges not the totals. I currently use
SUMIF('NC Sign'!$C$3:$C$12,PTS!$B$20,'NC Sign'!$D$3:$D$12)+SUMIF('NC Sign'!$E$31:$E$57,PTS!$B$20,'NC Sign'!$E$31:$E$57)+SUMIF('NC Sign etc....
This is very cumbersome and makes for some extremely long, slow formulas. Does anyone have a better way to do this. P.S. I need this to be accomplished in formula not VBA.
Solution 1:[1]
Maybe you may use this
=sum(filter({'NC Sign'!$D$3:$D$12,'NC Sign'!$E$31:$E$57,etc};{'NC Sign'!$C$3:$C$12,'NC Sign'!$E$31:$E$57,etc}='PTS'!$B$20))
But I don't know about how slow it is.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Jeremy Caney |