# Sumifs Formula

#### bolwahnn

##### New Member
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.

#### vogel997

##### Active Member
Do you have a sample of the raw data.?

#### bolwahnn

##### New Member
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 NoSchool4' list Location Code Sumif Total Formula LOCATION_CODE COUNT(*) Date 11/26/2013 CHI003 109 <-- =SUMIFS(\$B\$3:\$B\$19,\$A\$3:\$A\$19,G2,\$C\$3:\$C\$19,noschool4) CHI003 184 11/22/2013 11/27/2013 CHI035 4 <-- =SUMIFS(\$B\$3:\$B\$19,\$A\$3:\$A\$19,G3,\$C\$3:\$C\$19,noschool4) CHI003 183 11/23/2013 11/28/2013 CHI034 0 <-- =SUMIFS(\$B\$3:\$B\$19,\$A\$3:\$A\$19,G4,\$C\$3:\$C\$19,noschool4) CHI003 225 11/24/2013 11/29/2013 CHI003 116 11/25/2013 11/30/2013 CHI003 109 11/26/2013 12/1/2013 CHI003 232 11/27/2013 12/2/2013 CHI003 301 11/28/2013 CHI003 267 11/29/2013 CHI003 300 11/30/2013 CHI034 123 11/26/2013 CHI034 42 11/27/2013 CHI035 5 11/25/2013 CHI035 12 11/26/2013 CHI035 4 11/27/2013 CHI035 23 11/28/2013 CHI035 14 11/29/2013 CHI035 16 11/30/2013

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

#### Marcelo Branco

##### MrExcel MVP
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

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
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

##### MrExcel MVP
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.

Replies
10
Views
128
Replies
4
Views
96
Replies
3
Views
533
Replies
2
Views
242
Replies
1
Views
130

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.

### 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