SumIfs with multiple criteria from different sheets

Travis Ryan

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)

Jeff Reisman

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

Jeff Reisman

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)

Jeff Reisman

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

Travis Ryan

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?

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.

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.

Jeff Reisman

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)

Travis Ryan

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