Sumifs Formula

[bolwahnn]

Hope I can describe this accurately.....I'm trying to sum data on 2 criterias (Location Code, And Dates). I'm good with the location code because I have the range and I have the reference Location Code. I'm having an issue with the date's. Reason being that I have 2 ranges: I have the Raw data (contains all the data) and I have a list of dates that I want to sum from. On sumifs you have the Criteria Range and the Criteria, well I have the Criteria Range and the Criteria List. I thought that if I renamed the cells I would be able to sum the data that I needed, but I'm getting a 0.

Any ideas?

Heres the formula as a reference to what I'm doing....
=SUMIFS('Raw Data'!$G:$G,'Raw Data'!$A:$A,Sheet4!A24,'Raw Data'!$I:$I,NoSchool)

"NoSchool" Is the cell list of dates I renamed.

Appreciate any ideas on this.

Thanks,
Zeke

 

[vogel997]

Do you have a sample of the raw data.?

 

[bolwahnn]

Do you have a sample of the raw data.?

I've place it all on 1 sheet to show the raw data, the cell list of dates I'm trying to add and the formula I'm using. From the total, you can see its not adding up correctly.....This is just a small sample of a much larger data sample.

Raw Data</SPAN>

NoSchool4' list</SPAN>

Location Code</SPAN>

Sumif Total</SPAN>

Formula</SPAN>

LOCATION_CODE</SPAN>

COUNT(*)</SPAN>

Date</SPAN>

11/26/2013</SPAN>

CHI003</SPAN>

109</SPAN>

<--</SPAN>

=SUMIFS($B$3:$B$19,$A$3:$A$19,G2,$C$3:$C$19,noschool4)</SPAN>

CHI003</SPAN>

184 </SPAN>

11/22/2013</SPAN>

11/27/2013</SPAN>

CHI035</SPAN>

4</SPAN>

<--</SPAN>

=SUMIFS($B$3:$B$19,$A$3:$A$19,G3,$C$3:$C$19,noschool4)</SPAN>

CHI003</SPAN>

183 </SPAN>

11/23/2013</SPAN>

11/28/2013</SPAN>

CHI034</SPAN>

0</SPAN>

<--</SPAN>

=SUMIFS($B$3:$B$19,$A$3:$A$19,G4,$C$3:$C$19,noschool4)</SPAN>

CHI003</SPAN>

225 </SPAN>

11/24/2013</SPAN>

11/29/2013</SPAN>

CHI003</SPAN>

116 </SPAN>

11/25/2013</SPAN>

11/30/2013</SPAN>

CHI003</SPAN>

109 </SPAN>

11/26/2013</SPAN>

12/1/2013</SPAN>

CHI003</SPAN>

232 </SPAN>

11/27/2013</SPAN>

12/2/2013</SPAN>

CHI003</SPAN>

301 </SPAN>

11/28/2013</SPAN>

CHI003</SPAN>

267 </SPAN>

11/29/2013</SPAN>

CHI003</SPAN>

300 </SPAN>

11/30/2013</SPAN>

CHI034</SPAN>

123 </SPAN>

11/26/2013</SPAN>

CHI034</SPAN>

42 </SPAN>

11/27/2013</SPAN>

CHI035</SPAN>

5 </SPAN>

11/25/2013</SPAN>

CHI035</SPAN>

12 </SPAN>

11/26/2013</SPAN>

CHI035</SPAN>

4 </SPAN>

11/27/2013</SPAN>

CHI035</SPAN>

23 </SPAN>

11/28/2013</SPAN>

CHI035</SPAN>

14 </SPAN>

11/29/2013</SPAN>

CHI035</SPAN>

16 </SPAN>

11/30/2013</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL span=6></COLGROUP>

 

[Marcelo Branco]

Maybe...

H2
=SUMPRODUCT(--($A$3:$A$19=G2),--ISNUMBER(MATCH($C$3:$C$19,noschool4,0)),$B$3:$B$19)
copy down

M.

 

[bolwahnn]

Maybe...

H2
=SUMPRODUCT(--($A$3:$A$19=G2),--ISNUMBER(MATCH($C$3:$C$19,noschool4,0)),$B$3:$B$19)
copy down

M.

Formula didnt come back with the sum, came back with a result of "TRUE"......Diisregard this - I made a mistake - The Formula worked! Thank you!

 

[vogel997]

The "sum ifs" formula worked for me after a I changed the second date to the date formate. originally it was formatted as text, so it wouldn't work in the formula.

Raw Data
LOCATION_CODE	COUNT(*)	Date		NoSchool4' list	Location Code	Sumif Total		Formula
CHI003	184	11/22/13		11/26/13		CHI003	109	<--	=SUMIFS($B$3:$B$19,$A$3:$A$19,G3,$C$3:$C$19,E3)
CHI003	183	11/23/13		11/27/13		CHI035	4	<--	=SUMIFS($B$3:$B$19,$A$3:$A$19,G4,$C$3:$C$19,E4)
CHI003	225	11/24/13		11/28/13		CHI034	0	<--	=SUMIFS($B$3:$B$19,$A$3:$A$19,G5,$C$3:$C$19,E5)
CHI003	116	11/25/13		11/29/13
CHI003	109	11/26/13		11/30/13
CHI003	232	11/27/13		12/1/13
CHI003	301	11/28/13		12/2/13
CHI003	267	11/29/13
CHI003	300	11/30/13
CHI034	123	11/26/13
CHI034	42	11/27/13
CHI035	5	11/25/13
CHI035	12	11/26/13
CHI035	4	11/27/13
CHI035	23	11/28/13
CHI035	14	11/29/13
CHI035	16	11/30/13

<colgroup><col><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[Marcelo Branco]

Formula didnt come back with the sum, came back with a result of "TRUE"......Diisregard this - I made a mistake - The Formula worked! Thank you!

You are welcome and thanks for the feedback.

M.