# Sumifs not working

#### ExcelBlonde

##### New Member
Hi there,

I've used this formula in a workbook to calculate a figure based on 2 criteria. It works fine.

=SUMIFS(Data!\$AB\$2:\$AB\$200,Data!\$B\$2:\$B\$200,\$A\$1,Data!\$C\$2:\$C\$200,A4)

I've tried expanding on it within another workbook and it isn't working. Can anyone see any obvious mistakes?

=SUMIFS(Data!\$K\$2:\$K\$3518,Data!\$F\$2:\$F\$3518,"*"&A1&"*",Data!\$G\$2:\$G\$3518,"*"&B4&"*")

"*"&A1&"*" looks at a cell that has a date in it. The criteria it is searching has a date and time.
Likewise with "*"&B4&"*" it is looking for a specific word. The criteria it is searching has the word somewhere in the cell. Have I done this right?

I cant see whats wrong with it. It doesn't return an error. It returns 0.

Last edited:

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### AliGW

##### Banned
Can you give us a table of sample data from those ranges?

#### ExcelBlonde

##### New Member
 A B C D E F G H I J K L M N O P Data Data Data Data Date/Time in Date/Time due Data Data Data Data Count Data Data Data Data Data 123 123 123 123 13/12/2016 18:00 15/12/2016 18:00 Bob 123 123 123 4 123 123 123 123 123 231 231 231 231 14/12/2016 12:00 18/12/2016 14:00 Bob 231 231 231 2 231 231 231 231 231 456 456 456 456 13/12/2016 18:00 15/12/2016 18:00 Fred 456 456 456 1 456 456 456 456 456 789 789 789 789 13/12/2016 18:00 15/12/2016 14:00 Bob 789 789 789 5 789 789 789 789 789

<tbody>
</tbody>

I'm looking to find, for example, the number of items due 15/12/2016 with a name Bob. It should return 9.

#### AliGW

##### Banned
I hope this gives you the right guidance:

Excel 2016 (Windows) 32 bit
ABCDEFGHIJKLMNOP
212312312312313/12/2016 18:0015/12/2016 18:00Bob1231231234123123123123123
323123123123114/12/2016 12:0018/12/2016 14:00Bob2312312312231231231231231
445645645645613/12/2016 18:0015/12/2016 18:00Fred4564564561456456456456456
578978978978913/12/2016 18:0015/12/2016 14:00Bob7897897895789789789789789
6
79
815/12/2016
9Bob
Sheet2
Cell Formulas
RangeFormula
A7=SUMPRODUCT(ISNUMBER(SEARCH(A8,\$F\$2:\$F\$5,1))*(\$G\$2:\$G\$5=A9)*(\$K\$2:\$K\$5))

#### RoryA

##### MrExcel MVP, Moderator
"*"&A1&"*" looks at a cell that has a date in it. The criteria it is searching has a date and time.

That's the issue. Dates are stored as numbers and wildcards don't work with numbers. You can use SUMPRODUCT as Ali demonstrated.

#### FormR

##### MrExcel MVP
Hi - if you wanted to stick with SUMIFS() you could do it like this:

=SUMIFS(Data!\$K\$2:\$K\$3518,Data!\$F\$2:\$F\$3518,">="&A1,Data!\$F\$2:\$F\$3518,"<"&A1+1,Data!\$G\$2:\$G\$3518,"*"&B4&"*")

#### ExcelBlonde

##### New Member
Hi - if you wanted to stick with SUMIFS() you could do it like this:

=SUMIFS(Data!\$K\$2:\$K\$3518,Data!\$F\$2:\$F\$3518,">="&A1,Data!\$F\$2:\$F\$3518,"<"&A1+1,Data!\$G\$2:\$G\$3518,"*"&B4&"*")

That's great! It seems to work. I actually have a figure now rather than a 0. Sorry for the delay in response.

Thanks to all for your suggestions.

Replies
6
Views
558
Replies
0
Views
291
Replies
0
Views
454
Replies
2
Views
178
Replies
17
Views
1K

1,195,667
Messages
6,011,049
Members
441,580
Latest member
BornholmerBjarne

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back