SumIfs with multiple criteria from different sheets

Travis Ryan
Travis Ryan ✭✭
in Formulas and Functions

I am needing some assistance with Sumifs with multiple criteria from multiple sheets. We work out of a install and completed sheet for scheduling. I am trying to get the information from sales into a reference number sheet since reports don't give accurate average percentages.

This formula works fine for pulling YTD (granted i have to actually input "2022") however when I try to add in month to date it breaks and doesn't work. How would I input the Month into this?

SUMIFS({**ROL Install Master TOTALPRICE:TOTALPRICE}, {**ROL Install Master Range salesperson:salesperson}, =SalesID@row, {**ROL Install Master solddate:solddate}, YEAR(@cell) = 2022) + SUMIFS({**ROL Completed Install totalprice:totalprice}, {**ROL Completed Install salesperson:salesperson}, =SalesID@row, {**ROL Completed Install solddate:solddate}, YEAR(@cell) = 2022)



Tried this, but it get incorrect argument... it works untill i add ({**ROL Completed Install solddate:solddate}, YEAR(@cell) = 2022) to the first sumifs. It works when added to the colpleted part of the formula though for some reason. If it should be written a totally different way I am open to that as well. I need this formula as a base formula to help build all the others. My brain hurts....

=SUMIFS({**ROL Install Master TOTALPRICE:TOTALPRICE}, {**ROL Install Master Range salesperson:salesperson}, =SalesID@row, {**ROL Install Master solddate:solddate}, MONTH(@cell) = MONTH(TODAY()), {**ROL Completed Install solddate:solddate}, YEAR(@cell) = 2022) + SUMIFS({**ROL Completed Install totalprice:totalprice}, {**ROL Completed Install salesperson:salesperson}, =SalesID@row, {**ROL Completed Install solddate:solddate}, MONTH(@cell) = MONTH(TODAY()), {**ROL Completed Install solddate:solddate}, YEAR(@cell) = 2022)

· Share on FacebookShare on Twitter

Best Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/31/22 Answer ✓

    @Travis Ryan

    First thing, just to keep your brain from hurting this much, is that you don't need to use the ColumnName:ColumnName format when naming your ranges from other sheets. In fact, you could select your range from the other sheet and call the range {George} and it would work fine. My standard practice is to leave the sheet name in there and use an abbreviated name for the column that I understand (ex. for "Model Number" in the Product Data sheet I would use Product Data ModNum for the range name.)

    I noticed in your formula that in your second SUMIFS, the range you are adding up and the ranges for your criteria are all in the same sheet. In your first SUMIFS, you're trying to pull date criteria from both **ROL Install Master and **ROL Completed Install in the same SUMIFS, and that's definitely not going to work. Change your YEAR lookup to the ROL Install Master sold date and you should be good to go.

    image.png


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter
  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    https://community.smartsheet.com/discussion/comment/318095#Comment_318095

    Ah, I see. I think you'll need a helper column for this one. (I tried doing validation that there's a date in the solddate field inside the SUMIFS, but everything I tried failed 🙁. I know THIS next thing works though.)

    New column "HasSoldDate", with formula:

    =IF(ISDATE(solddate@row), "X")

    Then add that criteria and criteria range into your formula:

    SUMIFS({**ROL Install Master TOTALPRICE:TOTALPRICE}, {**ROL Install Master Range salesperson:salesperson}, =SalesID@row, {**ROL Install Master HasSoldDate}, "X", {**ROL Install Master solddate:solddate}, MONTH(@cell) = MONTH(TODAY()), {**ROL Install Master solddate:solddate}, YEAR(@cell) = 2022) + SUMIFS({**ROL Completed Install totalprice:totalprice}, {**ROL Completed Install salesperson:salesperson}, =SalesID@row, {**ROL Completed Install HasSoldDate}, "X", {**ROL Completed Install solddate:solddate}, MONTH(@cell) = MONTH(TODAY()), {**ROL Completed Install solddate:solddate}, YEAR(@cell) = 2022)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/31/22 Answer ✓

    @Travis Ryan

    First thing, just to keep your brain from hurting this much, is that you don't need to use the ColumnName:ColumnName format when naming your ranges from other sheets. In fact, you could select your range from the other sheet and call the range {George} and it would work fine. My standard practice is to leave the sheet name in there and use an abbreviated name for the column that I understand (ex. for "Model Number" in the Product Data sheet I would use Product Data ModNum for the range name.)

    I noticed in your formula that in your second SUMIFS, the range you are adding up and the ranges for your criteria are all in the same sheet. In your first SUMIFS, you're trying to pull date criteria from both **ROL Install Master and **ROL Completed Install in the same SUMIFS, and that's definitely not going to work. Change your YEAR lookup to the ROL Install Master sold date and you should be good to go.

    image.png


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter
  • Travis Ryan
    Travis Ryan ✭✭
    edited 01/31/22
    https://community.smartsheet.com/discussion/comment/318032#Comment_318032

    @Jeff Reisman

    Thanks so much. Cant believe I missed that. One issue I do have is that it appears to get an error if a row does not have a date. How would an iferror be interjected into this so that if the solddate is blank it doesnt error?

    · Share on FacebookShare on Twitter
  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    https://community.smartsheet.com/discussion/comment/318090#Comment_318090

    You should be able to wrap an IFERROR around each of the SUMIFS in the formula. Try it and see.

    =IFERROR(SUMIFS({**ROL Install Master TOTALPRICE:TOTALPRICE}, {**ROL Install Master Range salesperson:salesperson}, =SalesID@row, {**ROL Install Master solddate:solddate}, MONTH(@cell) = MONTH(TODAY()), {**ROL Install Master solddate:solddate}, YEAR(@cell) = 2022), "") + IFERROR(SUMIFS({**ROL Completed Install totalprice:totalprice}, {**ROL Completed Install salesperson:salesperson}, =SalesID@row, {**ROL Completed Install solddate:solddate}, MONTH(@cell) = MONTH(TODAY()), {**ROL Completed Install solddate:solddate}, YEAR(@cell) = 2022), "")

    Don't forget the , "") at the end of each IFERROR.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter
  • Travis Ryan
    Travis Ryan ✭✭
    https://community.smartsheet.com/discussion/comment/318093#Comment_318093

    @Jeff Reisman

    Thank you so much for your quick response! This community is amazing!

    Sorry, I think its close. It gets rid of the error but then it leaves the cell just blank. Perhaps I was incorrect in ny understanding of how the iferror works. I am just needing it to basically skip or not include a row if the solddate is blank. I still need the sum of things that fall into the criteria.

    · Share on FacebookShare on Twitter
  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    https://community.smartsheet.com/discussion/comment/318095#Comment_318095

    Ah, I see. I think you'll need a helper column for this one. (I tried doing validation that there's a date in the solddate field inside the SUMIFS, but everything I tried failed 🙁. I know THIS next thing works though.)

    New column "HasSoldDate", with formula:

    =IF(ISDATE(solddate@row), "X")

    Then add that criteria and criteria range into your formula:

    SUMIFS({**ROL Install Master TOTALPRICE:TOTALPRICE}, {**ROL Install Master Range salesperson:salesperson}, =SalesID@row, {**ROL Install Master HasSoldDate}, "X", {**ROL Install Master solddate:solddate}, MONTH(@cell) = MONTH(TODAY()), {**ROL Install Master solddate:solddate}, YEAR(@cell) = 2022) + SUMIFS({**ROL Completed Install totalprice:totalprice}, {**ROL Completed Install salesperson:salesperson}, =SalesID@row, {**ROL Completed Install HasSoldDate}, "X", {**ROL Completed Install solddate:solddate}, MONTH(@cell) = MONTH(TODAY()), {**ROL Completed Install solddate:solddate}, YEAR(@cell) = 2022)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter
  • Travis Ryan
    Travis Ryan ✭✭

    Thanks so much. I am not sure what I would do without the community here.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!