SUMIFS Formula not working

Farhana

= SUMIFS({2024 Expenses | Total Expenses}, {Staff Name | Name}, Name@row, {2024 Expenses | Type}, "STAFF - Medical", {2024 Expenses | YYYYMM}, YYYYMM@row, {2024 Expenses | Status}, "Approved")

Sheet 1

2024 Expenses:

(While Staff Name sheet is another sheet where we kept all Staff Name Details)

This is currently my formula where I'm trying to SUM the total expenses (to put at Medical Used) from different sheet that meets all the criteria listed as below:

Criteria & Range 1: {Staff Name | Name}, [Name]@row

Criteria & Range 2: {2024 Expenses | Type}, "STAFF - Medical"

Criteria & Range 3: {2024 Expenses | YYYYMM}, [YYYYMM]@row

Criteria & Range 4: {2024 Expenses | Status}, "Approved")

The '0' result proven that the formula I used is correct but it should be a total of 150 instead.

Can someone tell me whether which part should I correct?

SoS | Dan Palenchar

Hello @Farhana,

I can't say what is not working without seeing all of the ranges you are referencing in your formula, but I would suggest breaking your formula into 4 separate SUMIF() or SUMIFS() formulas for each range and seeing which are returning 0 values, this could help identify where the error is.

1. = SUMIFS({2024 Expenses | Total Expenses}, {Staff Name | Name}, Name@row)
2. = SUMIFS({2024 Expenses | Total Expenses}, {2024 Expenses | Type}, "STAFF - Medical")
3. = SUMIFS({2024 Expenses | Total Expenses}, {2024 Expenses | YYYYMM}, YYYYMM@row)
4. = SUMIFS({2024 Expenses | Total Expenses}, {2024 Expenses | Status}, "Approved")

Make sure that your ranges and criterion are truly identical, for example whatever make sure the format and column type of {Staff Name | Name} is identical to Name@row.

Hope that helps!

Farhana

Hey!

I decided to pull another criteria which is using Email instead of Name (idk why it didn't work bc both sheet using the same reference Name List) and the formula finally works!

Thanks!