Broken SUMIF Formula - Sum of column based on info in another column

DorothyWasHere

Hello everyone!

I am currently attempting a SUMIF equation that initially worked and is now broken. Here are the parameters.

I would like the sum of the dollar amount in this column "FY24 Adopted Budget" to show in a cell in this column, "July Expenditure" based on the information in these columns "Cost Center" (Dropdown List), "Object Code" (Dropdown list), "Date Created" (Date), "Project Description" (Dropdown List), and "Vendor" (Dropdown List).

Below is the updated formula I input in the one of the cells in the "July Expenditure" column.

=SUMIFS([FY24 Adopted Budget]:[FY24 Adopted Budget], [Cost Center]:[Cost Center], "280110", [Object Code]:[Object Code], "612120", [Date Created]:[Date Created], MONTH(@cell) = 7, [Project Description]:[Project Description], "Henry Ford", [Vendor]:[Vendor], "HENRY FORD HEALTH SYSTEM")

So basically I would like the sum all of the money spent on Henry Ford Health System for that object code and cost center in the month of July.

For some reason when I hit enter, I get the #INVALID DATA TYPE response and when I go to review the formula, the brackets around "Vendor" have disappeared.

Now, my original formula that included slightly different parameters worked just fine:

Sum of the dollar amount in this column "FY24 Adopted Budget" to show in a cell in this column, "July Expenditure" based on the information in these columns "Cost Center" (Dropdown List), "PO/Invoice" (Dropdown list), "Date Created" (Date), and "Project Description" (Dropdown List).

=SUMIFS([FY24 Adopted Budget]:[FY24 Adopted Budget], [Cost Center]:[Cost Center], "280110 HR Administration", [PO/Invoice]:[PO/Invoice], "Invoice", [Date Created]:[Date Created], MONTH(@cell) = 7 [Project Description]:[Project Description], "Henry Ford")

This formula worked and isn't that different from the new one shown up top. I can't figure out what I'm missing.

Hopefully this make sense!😅

KPH

I have three troubleshooting suggestions to try next.

Step 1

Can you check the format of the column you are putting the formula in matches the format of FY24 Adopted Budget? I think both should be Text/Number but there is a chance one is not.

If that looks fine..

Step 2

Can you check the data for oddities? Is there something in one of the columns that should not be there.

Step 3

Can you break down the formula and test each part to see where it gets upset?

Try

=SUMIFS([FY24 Adopted Budget]:[FY24 Adopted Budget], [Cost Center]:[Cost Center], "280110", [Object Code]:[Object Code], "612120", [Date Created]:[Date Created], MONTH(@cell) = 7, [Project Description]:[Project Description], "Henry Ford")

If that fails, try

=SUMIFS([FY24 Adopted Budget]:[FY24 Adopted Budget], [Cost Center]:[Cost Center], "280110", [Object Code]:[Object Code], "612120", [Date Created]:[Date Created], MONTH(@cell) = 7)

If that fails, try

=SUMIFS([FY24 Adopted Budget]:[FY24 Adopted Budget], [Cost Center]:[Cost Center], "280110", [Object Code]:[Object Code], "612120")

Keep stripping it back removing a range and criteria pair until it (hopefully) works. That should pinpoint where the problem is.

KPH

Hi @DorothyWasHere

The missing square brackets around vendor is not a problem. They are only needed if the column name contains spaces.

I actually think your formula is fine. It sounds like the Date Created column in the sheet you are now using is not set up as Date format. So the formula cannot extract a month from that column. Can you check?

DorothyWasHere

Hello @KPH ,

Thank you for your response!

I have checked and the column type reflects "Date."

KPH

I have three troubleshooting suggestions to try next.

Step 1

Can you check the format of the column you are putting the formula in matches the format of FY24 Adopted Budget? I think both should be Text/Number but there is a chance one is not.

If that looks fine..

Step 2

Can you check the data for oddities? Is there something in one of the columns that should not be there.

Step 3

Can you break down the formula and test each part to see where it gets upset?

Try

=SUMIFS([FY24 Adopted Budget]:[FY24 Adopted Budget], [Cost Center]:[Cost Center], "280110", [Object Code]:[Object Code], "612120", [Date Created]:[Date Created], MONTH(@cell) = 7, [Project Description]:[Project Description], "Henry Ford")

If that fails, try

=SUMIFS([FY24 Adopted Budget]:[FY24 Adopted Budget], [Cost Center]:[Cost Center], "280110", [Object Code]:[Object Code], "612120", [Date Created]:[Date Created], MONTH(@cell) = 7)

If that fails, try

=SUMIFS([FY24 Adopted Budget]:[FY24 Adopted Budget], [Cost Center]:[Cost Center], "280110", [Object Code]:[Object Code], "612120")

Keep stripping it back removing a range and criteria pair until it (hopefully) works. That should pinpoint where the problem is.

DorothyWasHere

Hello @KPH,

Step 2 seemed to solve the problem. I already had information in other cells of the FY24 Adopted Budget column. What I was trying to sum up was located rows below but in the same column. I didn't think that would be a problem but the formula didn't like that so I took the information of the I was trying to sum up and moved it to a new column called "Billed" and the formula I had worked like it was supposed to.

=SUMIFS(Billed:Billed, [Cost Center]:[Cost Center], "280110", [Object Code]:[Object Code], "612120", [Date Created]:[Date Created], MONTH(@cell) = 7, [Project Description]:[Project Description], "Henry Ford", Vendor:Vendor, "HENRY FORD HEALTH SYSTEM")

Thank you for your help!😀

KPH

Wonderful! Thanks for letting me know.