Sumifs Formula

[mojo300]

Am I able to do the following?

you're welcome

Ok,
So now I need a sumifs formula to add all the Sq. Ft. based on multiple criteria, Columns A and C. I tried the sumifs formula and I just couldnt get the numbers to add up correctly. Any help would be appreciated. THanks I did the first one manually

Book1
	A	B	C	D
1	Type	Number of Sites	State	Sq. Ft.
2	Double Park	8	OH	439500
3	Triple Park	35	OH
4	Single Park	5	OH
5	Double Park	9	PA
6	Triple Park	44	PA
7	Single Park	2	PA
8	Double Park	7	FL
9	Triple Park	55	FL
10	Single Park	0	FL
Sheet1

Cell Formulas
Range	Formula
B2	=COUNTIFS(Sheet3!A2:A166,A2:A10,Sheet3!C2:C166,C2)
B3	=COUNTIFS(Sheet3!A3:A167,A3:A11,Sheet3!C3:C167,C3)
B4	=COUNTIFS(Sheet3!A4:A168,A4:A12,Sheet3!C4:C168,C4)
B5	=COUNTIFS(Sheet3!A5:A169,A5:A13,Sheet3!C5:C169,C5)
B6	=COUNTIFS(Sheet3!A6:A170,A6:A14,Sheet3!C6:C170,C6)
B7	=COUNTIFS(Sheet3!A7:A171,A7:A15,Sheet3!C7:C171,C7)
B8	=COUNTIFS(Sheet3!A8:A172,A8:A16,Sheet3!C8:C172,C8)
B9	=COUNTIFS(Sheet3!A9:A173,A9:A17,Sheet3!C9:C173,C9)
B10	=COUNTIFS(Sheet3!A10:A174,A10:A18,Sheet3!C10:C174,C10)

This is the sheet where the numbers would come from

Book1
	A	B	C
1	Type	Sq. Ft.	State
2	Double Park	32536	OH
3	Double Park	156160	OH
4	Double Park	29040	OH
5	Double Park	15345	OH
6	Double Park	56139	OH
7	Double Park	69700	OH
8	Double Park	7450	OH
9	Double Park	73130	OH
10	Triple Park	7913	OH
11	Triple Park	10686	OH
12	Triple Park	19200	OH
13	Triple Park	11520	OH
14	Triple Park	49541	OH
15	Triple Park	11744	OH
16	Triple Park	13130	OH
17	Triple Park	6132	OH
18	Triple Park	43771	OH
19	Triple Park	17444	OH
20	Triple Park	905	OH
21	Triple Park	70089	OH
22	Triple Park	36147	OH
23	Triple Park	15517	OH
24	Triple Park	11790	OH
25	Triple Park	16131	OH
26	Triple Park	38287	OH
27	Triple Park	12725	OH
28	Triple Park	7922	OH
29	Triple Park	26250	OH
30	Triple Park	29738	OH
31	Triple Park	9268	OH
32	Triple Park	16923	OH
33	Triple Park	15423	OH
34	Triple Park	9670	OH
35	Triple Park	16192	OH
36	Triple Park	26764	OH
37	Triple Park	26486	OH
38	Triple Park	7546	OH
39	Triple Park	32604	OH
40	Triple Park	12600	OH
41	Triple Park	26664	OH
42	Triple Park	11033	OH
43	Triple Park	11620	OH
44	Triple Park	10057	OH
45	Single Park	65841	OH
46	Single Park	5248	OH
47	Single Park	1254	OH
48	Single Park	9657	OH
49	Single Park	33644	OH
50	Double Park	50805	PA
51	Double Park	19120	PA
52	Double Park	20731	PA
53	Double Park	191001	PA
54	Double Park	36655	PA
55	Double Park	16380	PA
56	Double Park	10548	PA
57	Double Park	14815	PA
58	Double Park	50560	PA
59	Triple Park	0	PA
60	Triple Park	26370	PA
61	Triple Park	21628	PA
62	Triple Park	14746	PA
63	Triple Park	22232	PA
64	Triple Park	151298	PA
65	Triple Park	21730	PA
66	Triple Park	341400	PA
67	Triple Park	30395	PA
68	Triple Park	17846	PA
69	Triple Park	0	PA
70	Triple Park	23104	PA
71	Triple Park	6609	PA
72	Triple Park	27842	PA
73	Triple Park	7497	PA
74	Triple Park	5640	PA
75	Triple Park	0	PA
76	Triple Park	10424	PA
77	Triple Park	17995	PA
78	Triple Park	15682	PA
79	Triple Park	8930	PA
80	Triple Park	28144	PA
81	Triple Park	1138	PA
82	Triple Park	0	PA
83	Triple Park	0	PA
84	Triple Park	74662	PA
85	Triple Park	930	PA
86	Triple Park	24780	PA
87	Triple Park	12904	PA
88	Triple Park	55388	PA
89	Triple Park	9666	PA
90	Triple Park	6201	PA
91	Triple Park	25896	PA
92	Triple Park	16676	PA
93	Triple Park	35115	PA
94	Triple Park	19865	PA
95	Triple Park	55467	PA
96	Triple Park	0	PA
97	Triple Park	26748	PA
98	Triple Park	38524	PA
99	Triple Park	0	PA
100	Triple Park	8826	PA
Sheet3

 

[Mahenderlalwani]

YOUR 1ST TABLE SHOWS DOUBLE PARK / TRIPLE PARK AND STATE FL 7/55 WHERE AS IN SHEET 3 TABLE THERE ARE NONE

EXCEL SHEET IS ATTACHED


HOPE THIS HELPS YOU

REGARDS
MAHENDER LALWANI

 

[mojo300]

YOUR 1ST TABLE SHOWS DOUBLE PARK / TRIPLE PARK AND STATE FL 7/55 WHERE AS IN SHEET 3 TABLE THERE ARE NONE

EXCEL SHEET IS ATTACHED


HOPE THIS HELPS YOU

REGARDS
MAHENDER LALWANI

You are correct as the MrExcel HTML Maker only allowed me to only copy a 100 lines, thats why the FL sites didnt carry over. I am fairly new to this, where are the formulas in the sheet you attached?

 

[mojo300]

Never mid i downloaded the document and saw them. My only question is that on my spreadsheet I have two tabs and for whatever reason They just wouldn't add up right, I wonder if I have to put the info all on one tab.

Thank you for your help though

 

[AlanY]

your formula should looks like

Excel 2013/2016
	A	B	C	D
1	Type	Number of Sites	State	Sq. Ft.
2	Double Park	8	OH	439500
3	Triple Park	35	OH	689432
4	Single Park	5	OH	115644
5	Double Park	9	PA	410615
6	Triple Park	44	PA	1212298
7	Single Park	2	PA	0
8	Double Park	7	FL	0
9	Triple Park	55	FL	0
10	Single Park	0	FL	0
Sheet1

Cell Formulas
Range	Formula
D2	=SUMIFS(Sheet3!B:B,Sheet3!A:A,A2,Sheet3!C:C,C2)

in B2 and copy down

 

[mojo300]

your formula should looks like

Excel 2013/2016
	A	B	C	D
1	Type	Number of Sites	State	Sq. Ft.
2	Double Park	8	OH	439500
3	Triple Park	35	OH	689432
4	Single Park	5	OH	115644
5	Double Park	9	PA	410615
6	Triple Park	44	PA	1212298
7	Single Park	2	PA	0
8	Double Park	7	FL	0
9	Triple Park	55	FL	0
10	Single Park	0	FL	0
Sheet1

Cell Formulas
Range	Formula
D2	=SUMIFS(Sheet3!B:B,Sheet3!A:A,A2,Sheet3!C:C,C2)

in B2 and copy down

Thank you

 

[AlanY]

you're welcome