Sumifs not working

[ExcelBlonde]

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.

 

[AliGW]

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

 

[ExcelBlonde]

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]

I hope this gives you the right guidance:

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Data	Data	Data	Data	Date/Time in	Date/Time due	Data	Data	Data	Data	Count	Data	Data	Data	Data	Data
2	123	123	123	123	13/12/2016 18:00	15/12/2016 18:00	Bob	123	123	123	4	123	123	123	123	123
3	231	231	231	231	14/12/2016 12:00	18/12/2016 14:00	Bob	231	231	231	2	231	231	231	231	231
4	456	456	456	456	13/12/2016 18:00	15/12/2016 18:00	Fred	456	456	456	1	456	456	456	456	456
5	789	789	789	789	13/12/2016 18:00	15/12/2016 14:00	Bob	789	789	789	5	789	789	789	789	789
6
7	9
8	15/12/2016
9	Bob
Sheet2

Cell Formulas
Range	Formula
A7	=SUMPRODUCT(ISNUMBER(SEARCH(A8,$F$2:$F$5,1))*($G$2:$G$5=A9)*($K$2:$K$5))

 

[RoryA]

"*"&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]

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]

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.