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

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 crosssheet 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

@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 crosssheet 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
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!