SUMIFS help

[Thomas Hochard]

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)

[#2 · Accepted solution]

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

[#3]

Hi Riny,

I got it to work and I appreciate your awesomeness!

Thank you, Thomas