# 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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,969
Messages
5,856,586
Members
431,822
Latest member
chaftun

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