SUMIFS works half the time

[birdec]

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.

 

[Jonmo1]

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?

 

[birdec]

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

Ok here's the actual Data:

API	Source	Test Type	Test Number	Number	Type	Depth Top	Depth Base	Fluid Amount	Fluid Units	Fluid Type	Propping Agent Amount	Propping Agent Units	Propping Agent Type	Calc for tons
49005268680001	PI	IP	001	2	FRAC	7416	7426	1040	BBL	FLUD	57360	LB	SAND	29
49005268680001	PI	IP	001	1	ACID	7416	7426	51	BBL	A
49005274350001	PI	IP	001	1	FRAC	7840	7870	67001	GAL	FLUD	125421	LB	SAND	63
49005517740000	PI	IP	001	1	FRAC	8202	8221
49005611750000	PI	IP	001	1	FRAC	8907	12102	21646	BBL	X-LINKGEL	779.1	TON	SAND	779
49005612270100	PI	IP	001	1	FRAC	9095	12436				979	TON	SANDRCSD	979
49005615470000	PI	IP	001	1	FRAC	8164	8208	99829	GAL	FLUD	184142	LB	SAND	92
49005616090000	PI	IP	001	1	FRAC	8218	8264	53886	GAL	FLUD	132960	LB	SAND	66
49005617200000	PI	IP	001	1	FRAC	8225	8249				187040	LB	SAND	94
49005618280000	PI	IP	001	1	FRAC	7000	7034	831	BBL	X-LINKGEL	72165	LB	SANDRCSD	36
49005622270000	PI	IP	001	1	FRAC	8650	8654	2472	BBL	X-LINKGEL	216450	LB	SAND	108
49005622270000	PI	IP	001	9	FRAC	10282	10286	2548	BBL	X-LINKGEL	227960	LB	SAND	114
49005622270000	PI	IP	001	11	FRAC	10640	10644	2525	BBL	X-LINKGEL	224850	LB	SAND	112
49005622270000	PI	IP	001	12	FRAC	10821	10825	2669	BBL	X-LINKGEL	223800	LB	SAND	112
49005622270000	PI	IP	001	13	FRAC	10999	11003	3306	BBL	X-LINKGEL	200800	LB	SAND	100
49005622270000	PI	IP	001	14	FRAC	11100	11104	2871	BBL	X-LINKGEL	150400	LB	SAND	75
49005622270000	PI	IP	001	15	FRAC	11335	11365	3389	BBL	X-LINKGEL	150130	LB	SAND	75
49005622270000	PI	IP	001	16	ACID	8650	8654	16	BBL	A
49005622270000	PI	IP	001	17	ACID	8870	8874	15	BBL	A
49005622270000	PI	IP	001	18	ACID	9166	9170	16	BBL	A
49005622270000	PI	IP	001	19	ACID	9388	9392	13	BBL	A
49005622270000	PI	IP	001	20	ACID	9567	9571	12	BBL	A
49005622270000	PI	IP	001	21	ACID	9747	9751	5	BBL	A
49005622270000	PI	IP	001	22	ACID	9926	9930	6	BBL	A
49005622270000	PI	IP	001	23	ACID	10028	10032	14	BBL	A
49005622270000	PI	IP	001	24	ACID	10282	10286	15	BBL	A
49005622270000	PI	IP	001	25	ACID	10462	10466	12	BBL	A
49005622270000	PI	IP	001	26	ACID	10640	10644	10	BBL	A
49005622270000	PI	IP	001	27	ACID	10821	10825	11	BBL	A
49005622270000	PI	IP	001	28	ACID	10999	11003	6	BBL	A
49005622270000	PI	IP	001	29	ACID	11100	11104	36	BBL	A
49005622270000	PI	IP	001	2	FRAC	8870	8874	2503	BBL	X-LINKGEL	227420	LB	SAND	114
49005622270000	PI	IP	001	30	ACID	11335	11365	16	BBL	A
49005622270000	PI	IP	001	3	FRAC	9166	9170	2534	BBL	X-LINKGEL	224895	LB	SAND	112
49005622270000	PI	IP	001	4	FRAC	9388	9392	2575	BBL	X-LINKGEL	226305	LB	SAND	113
49005622270000	PI	IP	001	5	FRAC	9567	9571	2591	BBL	X-LINKGEL	229020	LB	SAND	115
49005622270000	PI	IP	001	6	FRAC	9747	9751	2481	BBL	X-LINKGEL	225660	LB	SAND	113
49005622270000	PI	IP	001	7	FRAC	9926	9930	2572	BBL	X-LINKGEL	224190	LB	SAND	112
49005622270000	PI	IP	001	8	FRAC	10028	10032	2569	BBL	X-LINKGEL	223940	LB	SAND	112
49005622270000	PI	IP	001	10	FRAC	10462	10466	2549	BBL	X-LINKGEL	224120	LB	SAND	112
49009221960001	PI	IP	001	2	FRAC	9860	9875				162000	LB	SAND	81
49009221960001	PI	IP	001	1	ACID	9860	9875	1000	GAL	A
49009282200000	PI	IP	001	1	FRAC	10355	14302	29047	BBL	W	1440	TON	SAND	1440
49009282250000	PI	IP	001	1	FRAC	10678	14965	23938	BBL	FLUD	1192	TON	SAND	1192
49009282310000	PI	IP	001	1	FRAC	10645	14836	23929	BBL	FLUD	1192	TON	SAND	1192
49009282320000	PI	IP	001	1	FRAC	10560	14838	26429	BBL	W	1105	TON	SAND	1105
49009282330000	PI	IP	001	1	FRAC	10547	14693	23958	BBL	FLUD	1184	TON	SAND	1184
49009282430000	PI	IP	001	1	FRAC	10678	14484	26667	BBL	W	1175	TON	SAND	1175
49009282440000	PI	IP	001	1	FRAC	10441	14328	24761	BBL	W	1185	TON	SAND	1185
49009282450000	PI	IP	001	1	FRAC	10662	14594	780730	GAL	X-LINKGEL	1225	TON	SAND	1225
49009282560000	PI	IP	001	1	FRAC	10578	14437	23810	BBL	FLUD	1187	TON	SAND	1187
49009282580000	PI	IP	001	1	FRAC	10513	14522	24322	BBL	FLUD	1193	TON	SAND	1193
49009282680000	PI	IP	001	1	FRAC	10849	15238	24018	BBL	FLUD	1264.2	TON	SAND	1264
49009282690000	PI	IP	001	1	FRAC	10393	14613	23393	BBL	X-LINKGEL	1187.6	TON	SANDRCSD	1188
49009282700000	PI	IP	001	1	FRAC	10566	15022	23569	BBL	FLUD	1188	TON	SAND	1188
49009282980000	PI	IP	001	1	FRAC	10823	14882	888728	GAL	FLUD	1021.2	TON	SAND	1021
49009283040000	PI	IP	001	1	FRAC	10327	14602	22893	BBL	FLUD	1193.4	TON	SAND	1193
49009283060000	PI	IP	001	1	FRAC	11044	14994	19545	BBL	X-LINKGEL	1059	TON	SANDRCSD	1059
49009283080000	PI	IP	001	1	FRAC	9996	14113	23250	BBL	FLUD	1247	TON	SAND	1247
49009283090000	PI	IP	001	1	FRAC	11019	14756	23478	BBL	FLUD	1180.2	TON	SAND	1180
49009283100000	PI	IP	001	1	FRAC	10725	14499	22804	BBL	FLUD	1177.5	TON	SAND	1178
49009283130000	PI	IP	001	1	FRAC	11200	15095	29816	BBL	FLUD	1674	TON	SAND	1674
49009283150000	PI	IP	001	1	FRAC	11322	14851	22228	BBL	FLUD	1129.9	TON	SAND	1130
49009283160000	PI	IP	001	1	FRAC	10590	14574	22951	BBL	FLUD	1246	TON	SAND	1246
49009283210000	PI	IP	001	1	FRAC	10539	14716	23016	BBL	FLUD	1181.3	TON	SAND	1181
49009283220000	PI	IP	001	1	FRAC	10707	14897	22760	BBL	FLUD	1165	TON	SAND	1165
49009283250000	PI	IP	001	1	FRAC	10640	14643	21571	BBL	FLUD	1098	TON	SAND	1098
49009283260000	PI	IP	001	1	FRAC	10195	14146	22024	BBL	X-LINKGEL	1179	TON	SAND	1179
49009283450000	PI	IP	001	1	FRAC	9577	9607	3296	BBL	X-LINKGEL	272240	LB	SANDRCSD	136
49009284350000	PI	IP	001	1	FRAC	10475	14469	22777	BBL	X-LINKGEL	1349	TON	SAND	1349
49009284650000	PI	IP	001	1	FRAC	10481	14760	22843	BBL	X-LINKGEL	1238	TON	SAND	1238
49009284730000	PI	IP	001	1	FRAC	10761	14793	34817	BBL	X-LINKGEL	2003	TON	SANDRCSD	2003
49009284760000	PI	IP	001	1	FRAC	10598	14683	20697	BBL	FLUD	1245	TON	SAND	1245
49009284770000	PI	IP	001	1	FRAC	10368	19642	94809	BBL	X-LINKGEL	1192	TON	SAND	1192
49009284830100	PI	IP	001	1	FRAC	10520	15932	23909	BBL	X-LINKGEL	1249	TON	SAND	1249
49009284880000	PI	IP	001	1	FRAC	11010	15190	23482	BBL	X-LINKGEL	1331	TON	SANDRCSD	1331
49009284890000	PI	IP	001	1	FRAC	10391	14435	25078	BBL	X-LINKGEL	1239	TON	SANDRCSD	1239
49009285110000	PI	IP	001	1	FRAC	10870	15405	23392	BBL	X-LINKGEL	1310	TON	SANDRCSD	1310
49009285120000	PI	IP	001	1	FRAC	10647	15013	22949	BBL	X-LINKGEL	1302	TON	SAND	1302
49009285180000	PI	IP	001	1	FRAC	11066	15217	22288	BBL	X-LINKGEL	1351	TON	SAND	1351
49009285290000	PI	IP	001	1	FRAC	9457	20970	33187	BBL	X-LINKGEL	1031	TON	SAND	1031
49009285300000	PI	IP	001	1	FRAC	10800	15045	21844	BBL	X-LINKGEL	1226	TON	SANDRCSD	1226
49009285410000	PI	IP	001	1	FRAC	10516	14884	23381	BBL	X-LINKGEL	1299	TON	SANDRCSD	1299
49009285480000	PI	IP	001	1	FRAC	10473	14620	22166	BBL	X-LINKGEL	1335	TON	SAND	1335
49009285630000	PI	IP	001	1	FRAC	10630	14865	32144	BBL	X-LINKGEL	2001	TON	SANDRCSD	2001
49009285830000	PI	IP	001	1	FRAC	10820	14829	21810	BBL	X-LINKGEL	1279	TON	SAND	1279
49009286150000	PI	IP	001	1	FRAC	10154	14346	24845	BBL	FLUD	1376	TON	SAND	1376
49009286250000	PI	IP	001	1	FRAC	10485	14705	30031	BBL	X-LINKGEL	1091.4	TON	SAND	1091
49009286610000	PI	IP	001	1	FRAC	10617	14738	23157	BBL	X-LINKGEL	1167	TON	SAND	1167
49009286620000	PI	IP	001	1	FRAC	10973	15547	33544	BBL	X-LINKGEL	2167	TON	SANDRCSD	2167
49009286790000	PI	IP	001	1	FRAC	9798	16346	15228	BBL	X-LINKGEL	601	TON	SAND	601
49009287070000	PI	IP	001	1	FRAC	10575	14694	20316	BBL	X-LINKGEL	1226	TON	SAND	1226
49009287090000	PI	IP	001	1	FRAC	10535	14702	22110	BBL	X-LINKGEL	1367	TON	SAND	1367
49009287150000	PI	IP	001	1	FRAC	10937	15288	22414	BBL	X-LINKGEL	1347	TON	SAND	1347
49009287420000	PI	IP	001	1	FRAC	10470	15007	27417	BBL	FLUD	1510	TON	SAND	1510
49009287430000	PI	IP	001	1	FRAC	10137	14143	23986	BBL	X-LINKGEL	1213	TON	SANDRCSD	1213
49009287440000	PI	IP	001	1	FRAC	10743	14594	25798	BBL	X-LINKGEL	1086	TON	SANDRCSD	1086
49009287610000	PI	IP	001	1	FRAC	11131	15293	22195	BBL	X-LINKGEL	979	TON	SAND	979
49009287990000	PI	IP	001	1	FRAC	10202	14615	870500	GAL	X-LINKGEL	1250	TON	SANDRCSD	1250
49009288280000	PI	IP	001	1	FRAC	10425	18584	31954	BBL	X-LINKGEL	983	TON	SAND	983
49009288290000	PI	IP	001	1	FRAC	10125	14250	727022	GAL	X-LINKGEL	1163	TON	SANDRCSD	1163
49009288470000	PI	IP	001	1	FRAC	10655	16044	23255	BBL	X-LINKGEL	1377	TON	SAND	1377
49009288570000	PI	IP	001	2	FRAC	10165	14693	75391	BBL	X-LINKGEL	3076	TON	SAND	3076
49009288570000	PI	IP	001	1	ACID	10165	14693	814	BBL	A
49009289190000	PI	IP	001	1	FRAC	10440	20612	36392	BBL	X-LINKGEL	1218	TON	SANDRCSD	1218
49009289780000	PI	IP	001	1	FRAC	9040	14033	38100	BBL	X-LINKGEL	1388	TON	RCSD	1388
49009289800000	PI	IP	001	2	FRAC	10173	18914	79150	BBL	X-LINKGEL	4086	TON	SAND	4086
49009289800000	PI	IP	001	1	ACID	10173	18914	9689	GAL	A
49009289830000	PI	IP	001	2	ACID	10059	19016	3213	GAL	A
49009289830000	PI	IP	001	1	FRAC	10059	19016	110670	BBL	X-LINKGEL	5869	TON	SAND	5869
49009290130000	PI	IP	001	1	FRAC	10701	14558	11310	BBL	FLUD	429270	LB	SAND	215
49009290140000	PI	IP	001	1	FRAC	10396	14625	18316	BBL	X-LINKGEL	595	TON	SAND	595
49009290280000	PI	IP	001	2	FRAC	10098	15472	72611	BBL	X-LINKGEL	3556	TON	SAND	3556
49009290280000	PI	IP	001	1	ACID	10098	15472	10952	GAL	A
49009291000000	PI	IP	001	1	FRAC	10404	20852	34525	BBL	X-LINKGEL	1194	TON	SANDRCSD	1194
49009291900000	PI	IP	001	1	FRAC	10900	15471	14389	BBL	X-LINKGEL	980720	LB	SAND	490
49009292240000	PI	IP	001	10	FRAC	13065	13246	1381	BBL	X-LINKGEL	103340	LB	SANDRCSD	52
49009292240000	PI	IP	001	11	FRAC	13429	13567	1461	BBL	X-LINKGEL	87900	LB	SANDRCSD	44
49009292240000	PI	IP	001	12	FRAC	13753	13880	1987	BBL	X-LINKGEL	77120	LB	SANDRCSD	39
49009292240000	PI	IP	001	1	FRAC	10141	10278	1569	BBL	X-LINKGEL	104500	LB	SANDRCSD	52
49009292240000	PI	IP	001	2	FRAC	10459	10683	1622	BBL	X-LINKGEL	105600	LB	SANDRCSD	53
49009292240000	PI	IP	001	9	FRAC	12743	12923	1427	BBL	X-LINKGEL	102820	LB	SANDRCSD	51
49009292240000	PI	IP	001	4	FRAC	11096	11273	1435	BBL	X-LINKGEL	107536	LB	SANDRCSD	54
49009292240000	PI	IP	001	5	FRAC	11457	11595	1421	BBL	X-LINKGEL	104364	LB	SANDRCSD	52
49009292240000	PI	IP	001	6	FRAC	11781	11919	1506	BBL	X-LINKGEL	106620	LB	SANDRCSD	53
49009292240000	PI	IP	001	7	FRAC	12103	12241	1458	BBL	X-LINKGEL	106220	LB	SANDRCSD	53
49009292240000	PI	IP	001	8	FRAC	12425	12603	1573	BBL	X-LINKGEL	103200	LB	SANDRCSD	52
49009292240000	PI	IP	001	3	FRAC	10776	10956	1463	BBL	X-LINKGEL	106660	LB	SANDRCSD	53
49009292690000	PI	IP	001	2	FRAC	10311	15107	77070	BBL	X-LINKGEL	3063	TON	SAND	3063
49009292690000	PI	IP	001	1	ACID	10311	15107	34244	GAL	A
49009294800000	PI	IP	001	2	ACID	10300	14327	40320	GAL	A
49009294800000	PI	IP	001	1	FRAC	10300	14327	97042	BBL	X-LINKGEL	3913	TON	SAND	3913
49009295210000	PI	IP	001	2	FRAC	10283	18985	172991	BBL	X-LINKGEL	8037	TON	SAND	8037
49009295210000	PI	IP	001	1	ACID	10283	18985	61564	GAL	A
49009299210000	PI	IP	001	1	FRAC	10750	18482	168825	BBL	X-LINKGEL	1646	TON	SAND	1646
49009299210000	PI	IP	001	2	ACID	10750	18482	77	BBL	A
49019297950100	PI	IP	001	2	FRAC	9468	13897	38410	BBL	X-LINKGEL	1192	TON	SAND	1192
49019297950100	PI	IP	001	1	ACID	9468	13897	262	BBL	A

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

 

[Jonmo1]

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.

 

[birdec]

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

 

[birdec]

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>

 

[Jonmo1]

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

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