SUMIF Help
Hello, I am trying to figure how to write a sumif formula. I've attempted to copy examples from previous questions asked but I'm not able to get what I'm looking for.
I want to calculate the total amount if it was paid by (PN or Deluca) indicated by a check mark. Please disregard the course completed row.
Best Answer
-
Hey @ealexis
Sorry, one more question. When I look at your original screenshot, could the $7500 in the row be paid by both PN and DeLuca (a checkmark in both columns on the same row which means $15000 was paid?), or is it always one or the other
If you are looking for the SUM of each separate provider then your SUMIFS is (edited for cross sheet references. Don't forget you can't copy paste these in, you must create them)
=SUMIFS({source sheet Amount column}, {source sheet Amount column}, ISNUMBER(@cell), {source sheet Paid by PN column}, 1)
and DeLuca:
=SUMIFS({source sheet Amount column}, {source sheet Amount column}, ISNUMBER(@cell), {source sheet Paid by DeLuca column}, 1)
The ISNUMBER is there trying to weed out whatever is making the SUMIFS fail and give you a zero
Kelly
Answers
-
Hey @ealexis
Try this:
=SUMIFS(Amount:Amount, [Paid by PN]:[Paid by PN], 1) + SUMIFS(Amount:Amount, [Paid by PN]:[Paid by PN], <>1, [Paid by DeLuca]:[Paid by DeLuca], 1)
Will this work for you?
Kelly
-
Hi @Kelly Moore,
Its coming back as $0. It might be good to let you know that I am pulling the amounts from another sheet. Should it be SUMIFS and not SUMIF? I am just trying to pull one at a time. I will be putting paid by PN on one row and paid by deluca on another
-
Hey @ealexis
SUMIFS always works, regardless of number of critera, SUMIF works only with one. The second SUMIFS in the formula above requires SUMIFS. (I ALWAYS used SUMIFS regardless).
Two things.
- May I see your exact formula as you reference the other sheet
- just for a test, Sum your Amount column. =SUM({put in the range of your Amount column })
Please show me the 2nd formula as well. #2 is testing whether smartsheet believes the column to be 'summable'
Kelly
-
Hey @ealexis
Sorry, one more question. When I look at your original screenshot, could the $7500 in the row be paid by both PN and DeLuca (a checkmark in both columns on the same row which means $15000 was paid?), or is it always one or the other
If you are looking for the SUM of each separate provider then your SUMIFS is (edited for cross sheet references. Don't forget you can't copy paste these in, you must create them)
=SUMIFS({source sheet Amount column}, {source sheet Amount column}, ISNUMBER(@cell), {source sheet Paid by PN column}, 1)
and DeLuca:
=SUMIFS({source sheet Amount column}, {source sheet Amount column}, ISNUMBER(@cell), {source sheet Paid by DeLuca column}, 1)
The ISNUMBER is there trying to weed out whatever is making the SUMIFS fail and give you a zero
Kelly
-
@Kelly Moore that worked great. But I could not get it to work for something in the same sheet. I want to count the numbers of checks on the completed course column based on the names on the course. I tried countifs the same as I did with SUMIFS but it did not work.
-
Hey @ealexis
Not seeing your formula I have to guess that you either tried to insert the syntax of the SUMIFS into the COUNTIFS, or your ranges for same sheet formulas are not correct. It's always helpful to post formulas.
=COUNTIFS(Course:Course, Course@row, [Completed Course]:[Completed Course], 1)
Does this work for you?
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 199 Industry Talk
- 427 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!