Formula question  cross sheet reference with multiple criteria
Hi all,
I'm trying to calculate the total registration fees from confirmed people
=SUMIF({InPerson Registration Status}, "Confirmed",{Attendee Individual Cost}, > $0.00)
I have several needs to create cross sheet references that are looking for specific criteria and creating sums so any guidance would be much appreciated.
Answers

Hi @Melissa PB
The SUMIF function allows for one IF. If you want to look for more than one criterion then you need SUMIFS.
With SUMIF you need to specify the range to sum if it is not the range of the criterion.
If you want to use sum then the cost needs to be a number not text. This means $0.00 would be 0. You can make the cost appear with the dollar sign and the decimals in the sheet by using the currency format. In the formula, you need to use just numbers.
In your case, you could use
= SUMIF({InPerson Registration Status}, "Confirmed",{Attendee Individual Cost})
This would sum the range {Attendee Individual Cost} if the Status is Confirmed.
You only need to include the second criterion if the Cost could be less than 0 and you need to exclude negative amounts. In which case you need SUMIFS. The syntax is slightly different as the range to sum goes first.
=SUMIFS({Attendee Individual Cost}, {InPerson Registration Status}, "Confirmed", {Attendee Individual Cost}, >0)

Thank you KPH, I really appreciate the explanation. Very helpful as I learn more about formulas.
You mention that I need the currency converted to a number. I created another column in the sheet and tried:
=VALUE ([Individual Cost]@row)
I'm getting #INVALID VALUE
Will do more research but if you have a suggestion, I'd appreciate it!
Thank you again,
Melissa

Your costs might already be numbers in the sheet. An easy check is to do a simple plus =col1@row + col2@row.
If the cells are numbers you will get a number back, if they are text you will get 2 bits of text, one after the other.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!