Forum Discussion
Thomas Hochard
May 20, 2022Copper Contributor
SUMIFS help
I appreciate and thank you for your assistance with a resolution to this issue.
SUMIFS formula in #1 worksheet ‘PDS F1000 EX2-BUD TO ACT SMRY’ cell Q9 brings in the YTD total at $19,577,260 from #2 worksheet ‘Bureau Data’ for eight (8) bureaus.
In #1 worksheet ‘PDS F1000 EX2-BUD TO ACT SMRY’ cell C9, I would like the SUMIFS formula to pull in $702,777 from #2 worksheet ‘Bureau Data’ for this one (1) bureau.
I’ve tried multiple variations of this formula and it’s result is $0:
=SUMIFS('Bureau Data'!P:P,'Bureau Data'!$A:$A,'PDS F1000 EX2-BUD TO ACT SMRY'!$B$8,'Bureau Data'!$B:$B,'PDS F1000 EX2-BUD TO ACT SMRY'!$B$9)
Thomas Hochard On the Bureau Data sheet I would insert a column B where you enter the number for each bureau. Hide the column if you don't want to see it all the time.
Then the formula I C8 on the PDS sheet can be as follows (note that you don't need the sheet reference for the active sheet when you refer to B8 and B9 in the PDS sheet):
=SUMIFS('Bureau Data'!Q:Q,'Bureau Data'!$A:$A,$B9,'Bureau Data'!$B:$B,Sheet1!$B$8)
The formula you tried looks for the bureau number/name (B8 in the PDS sheet) in column A on the data sheet. But, there are no cells in that column A that contain the bureau numbers/name. Hence, zero. And that's why you need to insert a column where every row has the bureau number/name.
In summary, the above formula looks for the amounts in column Q (shifted one to the right), for items where column A contains the expense type AND column B the bureau.
Attached a mock-up of your file with a working example.
- Riny_van_EekelenPlatinum Contributor
Thomas Hochard On the Bureau Data sheet I would insert a column B where you enter the number for each bureau. Hide the column if you don't want to see it all the time.
Then the formula I C8 on the PDS sheet can be as follows (note that you don't need the sheet reference for the active sheet when you refer to B8 and B9 in the PDS sheet):
=SUMIFS('Bureau Data'!Q:Q,'Bureau Data'!$A:$A,$B9,'Bureau Data'!$B:$B,Sheet1!$B$8)
The formula you tried looks for the bureau number/name (B8 in the PDS sheet) in column A on the data sheet. But, there are no cells in that column A that contain the bureau numbers/name. Hence, zero. And that's why you need to insert a column where every row has the bureau number/name.
In summary, the above formula looks for the amounts in column Q (shifted one to the right), for items where column A contains the expense type AND column B the bureau.
Attached a mock-up of your file with a working example.
- Thomas HochardCopper ContributorHi Riny,
I got it to work and I appreciate your awesomeness!
Thank you, Thomas