SUMIF Help

ealexis

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.

Kelly Moore

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

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

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

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

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

@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

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