SUMIFS function ignoring ""

[dtrudo]

I am trying to use the SUMIFS function in Excel and it will not calculate as the function refers to "empty cells". I have no idea how to write this same function to ignore the empty cells so that it will calculate correctly. Help?

=SUMIFS('Production Data (BT)'!$E:$E,'Production Data (BT)'!$D:$D,"=1/*",'Production Data (BT)'!$B:$B,"Administrative Matter",'Production Data (BT)'!$C:$C,"Marissa")

- 'Production Data (BT) references another worksheet
- D:D references a date field (in this example I want to narrow down to all references to Jan (i.e. 1/*))
- "Administrative Matter" and "Marissa" are fields to key on so get specific information about the person I'm tracking numbers on.

Hope this helps.

[daddylonglegs]

Empty cells shouldn't stop that working......but if your date column contains actual dates you can't do it that way, try like this to sum for january 2010 only

=SUMIFS('Production Data (BT)'!$E:$E,'Production Data (BT)'!$D:$D,">="&DATE(2010,1,1),'Production Data (BT)'!$D:$D,"<="&DATE(2010,1,31),'Production Data (BT)'!$B:$B,"Administrative Matter",'Production Data (BT)'!$C:$C,"Marissa")

[dtrudo]

Thanks DLL. I copy/pasted your suggestion directly to the cell and it's still not working. I keep coming up with "0" even through the answer should total "4". The date field does have actual dates in this format: 1/1/2010 or 2/24/2010. I also tried several other months changing the dates where appropriate. I'm still missing something and the error icon still says I'm referring to empty cells (which may mean nothing...but it's still there. Any other suggestions?

[daddylonglegs]

I suggest you try leaving out the dates altogether. Do you get any result with just

=SUMIFS('Production Data (BT)'!$E:$E,'Production Data (BT)'!$B:$B,"Administrative Matter",'Production Data (BT)'!$C:$C,"Marissa")

or just

=COUNTIFS('Production Data (BT)'!$B:$B,"Administrative Matter",'Production Data (BT)'!$C:$C,"Marissa")

If you still get zero results then it may be that you don't have exact matches for "Marissa" and "Administrative Matter". Are you sure the spelling is identical - check you don't have any leading or trailing spaces which would invalidate any match

[dtrudo]

Thanks DLL. I took your advice (again) and found the problem. However, I want to give you some feedback as well. In the working formula below, I was able to get it working after adding a * before Administrative Matter considering other text preceded it. However, my date code of "=1/*" does work whereas I could not get your suggestion using &DATE to work. Here is the final working product:

=SUMIFS('Production Data (BT)'!$E:$E,'Production Data (BT)'!$D:$D,"=1/*",'Production Data (BT)'!$B:$B,"*Administrative Matter",'Production Data (BT)'!$C:$C,"Marissa*")

Thanks again for your support and I hope this helps others.

[dtrudo]

BTW...I should also add that the date column is set up as text and not date as your earlier post inquired. That may be the difference here. (just thought of it). If the column was listed as date, then your suggestion would have worked perfectly. Thanks again.