SUMIF after evaluating multiple Ranges
Does anyone know of a way to SUM a column in a sheet, after evaluating multiple other columns in another sheet to determine which column to sum? Essentially, I need something like an IF(SUMIF(..., but that isn't working for me.
The formula below is currently working in SHEET B to only look at a single column in SHEET B to SUM. The "97", "98", "102", etc. in the top rows of each sheet are the ID's that enable this to work. Originally there was only "Rate 1", but now we need to evaluate ID's for Rate 1, 2 and 3 to determine which "Net Monthly Rental" column to SUM.
=SUMIFS({SHEET A_Net Rental}, {SHEET A_InstallDate}, <>"", {SHEET A - Install ID}, <=[01/01/22]$1, {SHEET A_Removal ID}, >=[01/01/22]$1)
SHEET A
SHEET B
Thanks in advance for any suggestions!
Answers
-
What qualifier are you trying to use to determine which column you want to sum? I would suggest something like this where you have multiple if conditions that chooses which sumifs to use based on your if expressions. Each possible Column sumifs would be included and chosen based on whatever qualifier you use.
=IF(SUMIFS(x) = 1, SUMIFS(sheet a, conditions), IF(SUMIFS(y) = 2, SUMIFS(sheet b, conditions), IF(SUMIFS(z) = 3, SUMIFS(sheet c, conditions), "")))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 283 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!