# SUMIFS works half the time

#### birdec

##### New Member
My SUMIFS formula isn't working all the time.

On my 'Data' tab, I have the raw data for a project (though the columns aren't next to each other like this):

 Name Funny? (Column F) # of Jokes (Column O) Suzy No Bobby Yes 1400 Larry Yes 3500 Larry Yes 2600

<tbody>
</tbody>

On my 'Results' tab, I want to have a column (A:A) for each persons name and another column for their total # of jokes and want to show their total # of jokes only if they are funny. (If they're not funny then their "# of Jokes" cell is blank or zero).

 Name (Column A) # of Jokes Bobby 1400 Suzy 0 Larry 6100

<tbody>
</tbody>

This is my formula:
=SUMIFS(Data!O:O,Results!A:A,Results!A2,Data!F:F,"=Yes")

It works sometimes, other times it returns the incorrect value, and other times it returns 0 when the correct answer is nonzero.

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
For one thing, your formula is referencing different sheets.

It's summing # of Jokes from column O on a sheet named Data
But the criteria are being tested in column A and F on a sheet named Results

I assume that is just a result of you typing up a simplified version.
It would be best to paste in your ACTUAL formula

Other than that, inaccurate results means either
Some of the cells are not EXACTLY equal to Yes, they might be " Yes" or "Yes " with extra spaces.
Same thing for the names, maybe some are misspelled or have extra spaces as well.

Or the numbers in column O are not really numbers, but "numbers stored as Text"
Test for this with
=ISNUMBER(Data!O1) and fill down to the end.
Are they true or false, or mixture of both?

Names = API
Funny? = FRAC or ACID
# of Jokes = "Calc for tons"

Ok here's the actual Data:

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

Here's my Results tab:

 API Number Depth Lateral, ft Fracs Calc for tons 49005268680001 0 1 29 49005274350001 0 1 0 49005517740000 0 1 63 49005611750000 3863 1 0 49005612270100 4198 1 779 49005615470000 0 1 979 49005616090000 0 1 92 49005617200000 0 1 66 49005618280000 0 1 94 49005622270000 3273 15 36 49009215210001 0 0 108 49009221960001 0 1 114 49009282200000 4545 1 112 49009282250000 4835 1 112 49009282310000 4771 1 100 49009282320000 4791 1 75 49009282330000 4551 1 75 49009282370000 4535 0 0 49009282430000 4472 1 0 49009282440000 4445 1 0 49009282450000 4521 1 0 49009282470000 4405 0 0 49009282560000 4390 1 0 49009282580000 4386 1 0 49009282680000 4956 1 0 49009282690000 4542 1 0 49009282700000 5295 1 0 49009282980000 4624 1 0 49009283040000 4792 1 0 49009283060000 4680 1 0 49009283080000 4431 1 0 49009283090000 4653 1 114 49009283100000 4552 1 0 49009283130000 4676 1 112 49009283150000 4688 1 113 49009283160000 4380 1 115 49009283210000 4578 1 113 49009283220000 4604 1 112 49009283250000 4713 1 112 49009283260000 4505 1 112 49009283450000 0 1 81 49009284350000 4298 1 0 49009284650000 4814 1 1440 49009284730000 4516 1 1192 49009284760000 4486 1 1192 49009284770000 9776 1 1105 49009284830100 5694 1 1184 49009284880000 4762 1 1175 49009284890000 4407 1 1185 49009285110000 5278 1 1225 49009285120000 4851 1 1187 49009285180000 4723 1 1193 49009285290000 10924 1 1264 49009285300000 4901 1 1188 49009285410000 4756 1 1188 49009285480000 4395 1 1021 49009285630000 4819 1 1193 49009285830000 4444 1 1059 49009286150000 4481 1 1247 49009286250000 4682 1 1180 49009286610000 4511 1 1178 49009286620000 4908 1 1674 49009286790000 5897 1 1130 49009287070000 4444 1 1246 49009287090000 4403 1 1181 49009287150000 4983 1 1165 49009287420000 4812 1 1098 49009287430000 4131 1 1179 49009287440000 4482 1 136 49009287610000 5137 1 1349 49009287990000 4617 1 1238 49009288280000 8609 1 2003 49009288290000 4397 1 1245 49009288470000 5690 1 1192 49009288570000 4931 1 1249 49009289190000 10624 1 1331 49009289780000 4138 1 1239 49009289800000 9029 1 1310 49009289830000 9238 1 1302 49009290130000 4296 1 1351 49009290140000 4316 1 1031 49009290280000 5595 1 1226 49009291000000 10973 1 1299 49009291900000 5193 1 1335 49009292240000 4020 12 2001 49009292690000 5281 1 1279 49009294800000 4561 1 1376 49009295210000 9253 1 1091 49009299210000 9427 1 1167 49019056360001 0 0 2167 49019297950100 4904 1 601

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

For example: API 49009282680000 should return a value of 1264.2 but returns 0 which is incorrect.

ok, what's the actual formula you used?

How would you get a decimal value 1264.2
There are no decimal values in the 'Calc for tons' column.

=SUMIFS(Data!O:O,Results!A:A,Results!A2,Data!F:F,"=FRAC") where column O is the 'Calc for tons' and column F is the 'Type' column.

I tried putting it all one tab and I'm still getting the same answers. Not sure if multiple tabs matters or not.

I must've rounded decimal places in that column. The answer should be 1264 instead of 1264.2 then.

Think I figured it out: =SUMIFS(Data!O:O,Data!A:A,Results!A2)

I was using Results!A:A instead of Data!A:A.

These are my results, hopefully they match up with yours?

Thanks for helping me talk it out - go Aggies

 29 63 0 779 979 92 66 94 36 1600 0 81 1440 1192 1192 1105 1184 0 1175 1185 1225 0 1187 1193 1264 1188 1188 1021 1193 1059 1247 1180 1178 1674 1130 1246 1181 1165 1098 1179 136 1349 1238 2003 1245 1192 1249 1331 1239 1310 1302 1351 1031 1226 1299 1335 2001 1279 1376 1091 1167 2167 601 1226 1367 1347 1510 1213 1086 979 1250 983 1163 1377 3076 1218 1388 4086 5869 215 595 3556 1194 490 608 3063 3913 8037 1646 0 1192

<colgroup><col></colgroup><tbody>
</tbody>

Yes, it's because of the wrong sheet references...

Should be
=SUMIFS(Data!O:O,Data!A:A,Results!A2,Data!F:F,"=FRAC")

Replies
2
Views
348
Replies
6
Views
562
Replies
2
Views
155
Replies
4
Views
231
Replies
1
Views
388

1,196,154
Messages
6,013,756
Members
441,781
Latest member
Gian Carlos

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