Double Count If

M

mojo300

Active Member
Joined
Apr 18, 2006
Messages
301
Is there a way to do a double Count If formula?

Book1
ABC
1TypeNumber of SitesState
2Double Park8OH
3Triple Park35OH
4Single Park5OH
5Double Park44PA
6Triple Park36PA
7Single Park2PA
8Double Park6FL
9Triple Park22FL
10Single Park1FL
Sheet1
Cell Formulas
RangeFormula
B2=COUNTIF(Sheet3!A2:A9,A2)
B3=COUNTIF(Sheet3!A10:A49,A3)
B4=COUNTIF(Sheet3!A45:A49,A4)


i did the first 3 manually in B2, B3, B4
I filtered the sites by state and then manually selected the sites in A2, A3, A4, but what I need to be able to do is for the countif to check how many sites are in each state

So for OH I have:
8 Sites of DOUBLE PARK
35 Sites of TRIPLE PARK
5 Sites of SINGLE PARK

Can i have the formula count the number of each of those sites per State without having to sort them by state first and manually selecting?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
try the countifs() function with State as your second criteria
 
Upvote 0
AlanY said:
you're welcome
Click to expand...

Ok,
So now I need a sumifs formula to add all the Sq. Ft. based on the same criteria, 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
ABCD
1TypeNumber of SitesStateSq. Ft.
2Double Park8OH439500
3Triple Park35OH
4Single Park5OH
5Double Park9PA
6Triple Park44PA
7Single Park2PA
8Double Park7FL
9Triple Park55FL
10Single Park0FL
Sheet1
Cell Formulas
RangeFormula
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
ABC
1TypeSq. Ft.State
2Double Park32536OH
3Double Park156160OH
4Double Park29040OH
5Double Park15345OH
6Double Park56139OH
7Double Park69700OH
8Double Park7450OH
9Double Park73130OH
10Triple Park7913OH
11Triple Park10686OH
12Triple Park19200OH
13Triple Park11520OH
14Triple Park49541OH
15Triple Park11744OH
16Triple Park13130OH
17Triple Park6132OH
18Triple Park43771OH
19Triple Park17444OH
20Triple Park905OH
21Triple Park70089OH
22Triple Park36147OH
23Triple Park15517OH
24Triple Park11790OH
25Triple Park16131OH
26Triple Park38287OH
27Triple Park12725OH
28Triple Park7922OH
29Triple Park26250OH
30Triple Park29738OH
31Triple Park9268OH
32Triple Park16923OH
33Triple Park15423OH
34Triple Park9670OH
35Triple Park16192OH
36Triple Park26764OH
37Triple Park26486OH
38Triple Park7546OH
39Triple Park32604OH
40Triple Park12600OH
41Triple Park26664OH
42Triple Park11033OH
43Triple Park11620OH
44Triple Park10057OH
45Single Park65841OH
46Single Park5248OH
47Single Park1254OH
48Single Park9657OH
49Single Park33644OH
50Double Park50805PA
51Double Park19120PA
52Double Park20731PA
53Double Park191001PA
54Double Park36655PA
55Double Park16380PA
56Double Park10548PA
57Double Park14815PA
58Double Park50560PA
59Triple Park0PA
60Triple Park26370PA
61Triple Park21628PA
62Triple Park14746PA
63Triple Park22232PA
64Triple Park151298PA
65Triple Park21730PA
66Triple Park341400PA
67Triple Park30395PA
68Triple Park17846PA
69Triple Park0PA
70Triple Park23104PA
71Triple Park6609PA
72Triple Park27842PA
73Triple Park7497PA
74Triple Park5640PA
75Triple Park0PA
76Triple Park10424PA
77Triple Park17995PA
78Triple Park15682PA
79Triple Park8930PA
80Triple Park28144PA
81Triple Park1138PA
82Triple Park0PA
83Triple Park0PA
84Triple Park74662PA
85Triple Park930PA
86Triple Park24780PA
87Triple Park12904PA
88Triple Park55388PA
89Triple Park9666PA
90Triple Park6201PA
91Triple Park25896PA
92Triple Park16676PA
93Triple Park35115PA
94Triple Park19865PA
95Triple Park55467PA
96Triple Park0PA
97Triple Park26748PA
98Triple Park38524PA
99Triple Park0PA
100Triple Park8826PA
Sheet3
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Number series based on multiple criteria
Replies
3
Views
167
SanjayGMusafir
SanjayGMusafir
A
  • Question
Can someone help find the Excel formula
Replies
5
Views
220
Cubist
Cubist
C
  • Solved
Unable to copy formula down sheet if data isn't there.
Replies
3
Views
127
Fluff
Fluff
C
  • Solved
Formula assistance - identifying full or part counts
Replies
5
Views
225
RobP
R
S
  • Locked
  • Question
A Different way to Calculate Ratios using GDC & or MIN ?
Replies
1
Views
192
Fluff
Fluff

Forum statistics

Threads
1,220,965
Messages
6,157,119
Members
451,399
Latest member
alchavar

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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