Sumifs matching 2 criteria

[byrdieep]

I have data:
D = Amount (currency)
E = Check number OR "CASH" (text)
F = Date (at this point two options are 12/10/2015 OR 1/14/2016 (special m/d/yyyy)
The data is in rows 2 - 203 (row 1 is headings)

I want to sum the total of the "cash" received on 1/14/2016.

I typed this, but the answer is coming out as 0.00, but there are at least 7 cells matching BOTH those criteria.

=SUMIFS(D2:D203,e2:e203,”CASH”,f2:f203,”2/14/2016”)

What is wrong with that? I'm using office 2010 on a windows 8 interface.

All the text "CASH" is in UPPERCASE... would it make a difference if it were mixed case?

Thanks so much!

 

[jtakw]

Hi,

Is this a typo?, you stated "I want to sum the total of the "cash" received on 1/14/2016.",
but your formula: "=SUMIFS(D2:D203,e2:e203,”CASH”,f2:f203,”2/14/2016”)" refers to a different date?

 

[gaz_chops]

Remove the quotes from the date, 1/14/2016

 

[jtakw]

Remove the quotes from the date, 1/14/2016

Removing the quotes didn't work for me, but the OPs original formula works if the date is correct...

Column D formatted as Currency, E as Text, F as Date

Excel 2010
	D	E	F	G	H	I
1	Amount	CK #/Cash	Date
2	$10.00	1234	12/10/2015		110	0
3	$20.00	CASH	1/14/2016
4	$30.00	2345	1/14/2016
5	$40.00	3456	12/10/2015
6	$50.00	cash	12/10/2015
7	$60.00	Cash	12/10/2015
8	$70.00	4567	1/14/2016
9	$80.00	5678	1/14/2016
10	$90.00	CasH	1/14/2016
Sheet1

Cell Formulas
Range	Formula
H2	=SUMIFS(D2:D10,E2:E10,"CASH",F2:F10,"1/14/2016")
I2	=SUMIFS(D2:D10,E2:E10,"CASH",F2:F10,1/14/2016)

 

[byrdieep]

copying and pasting and adjusting the H2 line worked... I can't tell how that is different than what I had (other than my date discrepancy - BUT I had the formula in 2 cells, on with the January date, and it wasn't working either). Oh well.... At least its working NOW. Thanks so much to everyone.

 

[FDibbins]

if you remove the quotes, excel will treat that as a calc...2 div 14 div 2016 = 0.0000708616780045351
2/14/2016

 

[byrdieep]

That make lots of sense, once you think about it! Thanks. I've learned SO MUCH on this board.

 

[jtakw]

copying and pasting and adjusting the H2 line worked... I can't tell how that is different than what I had (other than my date discrepancy - BUT I had the formula in 2 cells, on with the January date, and it wasn't working either). Oh well.... At least its working NOW. Thanks so much to everyone.

You're welcome, I think I know why your formula didn't work and copying and pasting mine did.
Notice your quote marks in your original formula in Post #1 =SUMIFS(D2:D203,e2:e203,”CASH”,f2:f203,”2/14/2016”)
are not the same as

=SUMIFS(D2:D10,E2:E10,"CASH",F2:F10,"1/14/2016")

Since the quote marks in your formula are not the "standard" quote marks used by Excel, Excel does not recognize them.
Don't know how you got those, maybe copying from another source, but I've read somewhere this will cause problems.