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!