Using Formulas with cross reference sheets

Angela Hollingsworth · 03/21/24

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

brianschmidt · 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!:)

Angela Hollingsworth · 03/21/24

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

Angela Hollingsworth · 03/22/24

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 · 03/25/24

@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!:)

Using Formulas with cross reference sheets

Answers

Help Article Resources

Categories