SUMIFS to include only blank field for one of the criteria

[tadaas]

I am looking to utilize the SUMIFS formula for my time tracking spreadsheet. I have one criteria to sum values that are equal to a certain value "V" for vacation, and "D" for Dr Appointment. I am not trying to include the criteria that sums those values if they haven't been submitted, so I can keep track of future values.

I've looked through other posts, and saw responses... but they don't work for me.


Here's a mock formula:

=SUMSF(Type Range, Type, Hrs, Submitted Date, Blank)

=SUMIFS(B$51:B$62,E2,C$51:C$62,D51:D52, ?)

Any help would be greatly appreciated

 

[Jonmo1]

Welcome to the board

Try
=SUMIFS(B$51:B$62,E2,C$51:C$62,D51:D62, "")

 

[tadaas]

I've tried:

=SUMIFS(B$51:B$62,E2,C$51:C$62,D51:D62,"=")

=SUMIFS(B$51:B$62,E2,C$51:C$62,D51:D62,"")

Neither works. And actually, I just noticed an error.. should be d62 (but that wasn't part of the issue.

I'm still getting #VALUE!

 

[AlanY]

you got the range and criteria the other way round, try

=SUMIFS(B$51:B$62,C$51:C$62,E2,D51:D62,"")

 

[tadaas]

Weird.. I utilized the "Insert Formula" function and that's how the formula came up. Now I don't have an error, but doesn't seem to be Summing up the values. Just showing "0"

=SUMIFS(B$51:B$62,C$51:C$62,E2,D51:D62,"")


-- removed inline image ---

 

[Jonmo1]

OK, rather than taking a formula that doesn't work and trying to 'fix' it..

Can you just state each range and criteria?
Which range do you want to be summed?
Which ranges contain which criteria?

The Syntax goes
=SUMIFS(RangeToSum,Criteria1Range,Criteria1,Criteria2Range,Criteria2)

 

[AlanY]

sorry, should be

=SUMIFS(B$51:B$62,C$51:C$62,E2,D51:D62,"<>")

 

[tadaas]

AlanY... still results in "0"

Jonmo... the formula I came up with came from the Insert Formula function... utilizing your default

=SUMIFS(RangeToSum,Criteria1Range,Criteria1,Criteria2Range,Criteria2)
RangetoSum = c51:c62
Criteria1Range = b51:b62
Criteria1 = e2
Criteria2Range = d51:d62
Criteria2 = "" (= blank)

=SUMIFS(C51:C62, B51:B62, E2, d51:d62,"")

I tried the above formula using "=" and this worked. Thanks everyone for your assistance. Apparently the Insert Function shouldn't be utilized.

 

[AlanY]

AlanY... still results in "0"

weird! with this?

sorry, should be

=SUMIFS(B$51:B$62,C$51:C$62,E2,D51:D62,"<>")

 

[tadaas]

It worked with the "" after I had the formula correct without using the "Insert Function". When I utilized the proper syntax... all worked as intended