Sumifs Formula

bolwahnn

New Member
Joined
Apr 29, 2011
Messages
40
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

bolwahnn

New Member
Joined
Apr 29, 2011
Messages
40
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,524
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Maybe...

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

M.
 

bolwahnn

New Member
Joined
Apr 29, 2011
Messages
40

ADVERTISEMENT

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!
 
Last edited:

vogel997

Active Member
Joined
Jan 22, 2010
Messages
412
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_CODECOUNT(*)DateNoSchool4' listLocation CodeSumif TotalFormula
CHI00318411/22/1311/26/13CHI003109<--=SUMIFS($B$3:$B$19,$A$3:$A$19,G3,$C$3:$C$19,E3)
CHI00318311/23/1311/27/13CHI0354<--=SUMIFS($B$3:$B$19,$A$3:$A$19,G4,$C$3:$C$19,E4)
CHI00322511/24/1311/28/13CHI0340<--=SUMIFS($B$3:$B$19,$A$3:$A$19,G5,$C$3:$C$19,E5)
CHI00311611/25/1311/29/13
CHI00310911/26/1311/30/13
CHI00323211/27/1312/1/13
CHI00330111/28/1312/2/13
CHI00326711/29/13
CHI00330011/30/13
CHI03412311/26/13
CHI0344211/27/13
CHI035511/25/13
CHI0351211/26/13
CHI035411/27/13
CHI0352311/28/13
CHI0351411/29/13
CHI0351611/30/13

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

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,524
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top