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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 03/21/24 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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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

  • ealexis
    ealexis ✭✭

    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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 03/21/24

    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.

    1. May I see your exact formula as you reference the other sheet
    2. 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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 03/21/24 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

  • ealexis
    ealexis ✭✭

    @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.



  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!