How to SUMIF but multiply 2 columns to get the SUMS?
Basically I have the following columns in a smartsheet (see example below).
I want to sum the Quote$ multiplied by the PWIN to get the weighted value for the next 30 days.
Here's what I was thinking.. what am I doing wrong?
=SUMIF ( {Quote$}*{PWIN}, {Date of Award}, Today(30) )
Quote$ | PWIN | Date of Award
--------------------------------------
$100 | 10% | 10/1/2022
$200 | 50% | 9/1/2022
$300 | 100% | 9/5/2022
Answers
-
Hi @KFrank,
It's probably easier to use a helper column on this (it also lets you easily spot any errors). If you're wanting to check only for the next 30 days and the sheet will end up with historical data (i.e. things aren't deleted), use this formula in the extra column:
=IF(AND([Date of Award]@row >= TODAY(), ([Date of Award]@row <= TODAY(30))), ([Quote$]@row * PWIN@row), "")
You can then use a simple SUM to total the results from this column:
=SUM([Helper]:[Helper]
If your table also has multiple people in it, you can then use this as as the basis for looking each person's totals with a SUMIF.
Hope this is of assistance - any questions let me know!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!