Using Formulas with cross reference sheets

Hello,

I need help adding one more criterion to this formula I am using to pull data from one sheet (Budget) and give me a total expense in another sheet (Staffing).

=SUMIF({5320182 VENDOR}, @cell = [Last Name]@row, {5320182 EXPENSE})

This formula is pulling the data fine but I need to narrow it down one more time by searching another column in the BUDGET sheet called SUBCODE - but there are at least 2 if not up 6 different subcodes to search. ie 5111 and 5112, or 5011, 5012, 5111 and 5112, etc.

I tried various versions (in front, middle and end) of adding this to the formula to narrow to one subcode total with no success.

{5320182 SUBCODES}, @cell = 5111

{5320182 SUBCODES}, @cell = "5111"

{5320182 SUBCODES}, = 5111


Thank you!

Angela

Answers

  • brianschmidt
    brianschmidt ✭✭✭✭✭
    edited 03/21/24

    It looks like you are looking to either use a SUMIFS (note the "S" on the end) or a SUM(COLLECT formula. Both allow you to add multiple criteria. Using the SUMIFS formula, I would try this:

    =SUMIFS({5320182 EXPENSE}, {5320182 VENDOR}, @cell = [Last Name]@row, {5320182 SUBCODES}, @cell = "5111")

    Hope this helps!:)

  • Thank you so much for the quick reply Brian.

    I tried this and I got a 0 when I should of had a total of around $110,000, better than an invalid.

    I will try a SUM(COLLECT too.

    Also, what if I need the range to include 5112 with 5111 or even broader 5011, 5012, 5111 and 5112?


    Angela

  • Hello -

    A few more questions in this area:

    1) I use this in a sheet that pulls from inside that sheet works great:

    =SUMIFS(Spent:Spent, [Areas of Project Spending]:[Areas of Project Spending], @cell = PERSONNEL@row, [Expense Date]:[Expense Date], AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 12, 31)))

    I am trying to do the same thing with a cross-sheet reference:

    =SUMIFS({5330416 VENDOR}, =Person@row, {5330416 EXPENSE}, {5330416 DATE}, AND(@cell >= DATE(2024, 1, 1), @cell <= DATE(2024, 1, 30)))

    Comes up blank - can get it to work without the {5330416 DATE}, AND(@cell >= DATE(2024, 1, 1), @cell <= DATE(2024, 1, 30))) part but need the specific date ranges.


    2) Still curious about the question above:

    =SUMIFS({5320182 EXPENSE}, {5320182 VENDOR}, @cell = [Last Name]@row, {5320182 SUBCODES}, @cell = "5111")

    tried a SUM(Count with no success, the equation provided gave me a 0 - need to also search by multiple criteria to include 5112 with 5111 or even broader 5011, 5012, 5111 and 5112


    Thanks!

    Angela

  • brianschmidt
    brianschmidt ✭✭✭✭✭

    @Angela Hollingsworth Looks like I totally missed your comment here. Sorry about that!😳

    1) Just looking at the order of your formula, it looks like you might have some things flipped around. Assuming the cross-sheet reference with the word expense is what you are trying to sum, I would try this:

    =SUMIFS({5330416 EXPENSE}, {5330416 VENDOR}, Person@row, {5330416 DATE}, AND(@cell >= DATE(2024, 1, 1), @cell <= DATE(2024, 1, 30)))

    If issues with the date persist, you could also try breaking out the date criterion as follows (though it really should work with the AND function as written...):

    =SUMIFS({5330416 EXPENSE}, {5330416 VENDOR}, Person@row, {5330416 DATE}, >= DATE(2024, 1, 1), {5330416 DATE}, <= DATE(2024, 1, 30))

    2) For this, I would rewrite and include additional subcodes as follows:

    =SUMIFS({5320182 EXPENSE}, {5320182 VENDOR}, [Last Name]@row, {5320182 SUBCODES}, OR(@cell = "5111", @cell="5112", @cell="5011", @cell="5012"))

    Let me know if I missed anything. Thanks!:)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!