More Count Help please....

devans

New Member
Joined
Feb 5, 2004
Messages
13
ok. I am setting up a count statement based on two columns, column A has the words Filled or Open, column I has numbers. I want a formula that will say If column A says Filled AND Column I has the numbers 10 - 17, count. It's the Column A part of the formula that I'm struggling with. I can write the COUNTIF formula to count the number, but not how to relate it to what's in C. A.....
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
devans said:
ok. I am setting up a count statement based on two columns, column A has the words Filled or Open, column I has numbers. I want a formula that will say If column A says Filled AND Column I has the numbers 10 - 17, count. It's the Column A part of the formula that I'm struggling with. I can write the COUNTIF formula to count the number, but not how to relate it to what's in C. A.....

Welcome to the board -- can we interest you in something in the SUMPRODUCT line?

=SUMPRODUCT(--($A$1:$A$100="Filled"),--($I$1:$I$100>=10),--($I$1:$I$100<=17))

or

=SUMPRODUCT(--($A$1:$A$100=$B$1),--($I$1:$I$100>=$C$1),--($I$1:$I$100<=$D$1))

where B1, C1 and D1 house criteria {Filled,10,17}
 

devans

New Member
Joined
Feb 5, 2004
Messages
13
Does Sumproduct just work with numbers? Now in Col. I, need to count the number of times G1, G2, G3, G4, G5, G6, AND 1-9 occur with the word "Filled" in Col. A. I thought I could modify the SP stmt you suggested, but it's not working - returns a zero (wrong answer - at leat 50 or so). Here's what I wrote:

=SUMPRODUCT(--($A$2:$A$500="Filled"),--($I$2:$I$500="G1"),--($I$2:$I$500=G2),--($I$2:$I$500=G3),--($I$2:$I$500=G4),--($I$2:$I$500=G5),--($I$2:$I$500=G6),--($I$2:$I$500<=9))
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

Well, first off, you are checking to see if the values in I are = to G1 AND G2 AND... obviously, they can't be = to more than one of them, assuming they are different values in G; What you have done is an AND'ing when I think you want part AND and part OR [the G-range part]. Is this correct?

EDIT -- You are also checking for a literal "G1", not the contents of cell G1.
 

devans

New Member
Joined
Feb 5, 2004
Messages
13
Sorry, I was not clear. I was not referring to col. G, but rather the text "G1", "G2", etc. i.e., Col. I has in it: G1 - G6 (text), and the numbers 2-22. I'm counting Col. I in three segments, seg. 1 is all the G1-G6 and 2-9, seg. 2 is 10-17, seg. 3 is 18-22. I got the right formula for seg. 2 and seg. 3 to work using Sumproduct, but STUCK on the formula for seg. 1.

THANKs for all you help You guys must wear shining armor.....
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

Like this?

=SUMPRODUCT(--($A$2:$A$500="Filled")*($I$2:$I$500={"G1","G2","G3","G4","G5","G6",2,3,4,5,6,7,8,9}))
 

devans

New Member
Joined
Feb 5, 2004
Messages
13
IT WORKED. modified this one for another formula count text based on what is in another column. MANY THANKS!!! :biggrin:
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
just_jon said:
Like this?

=SUMPRODUCT(--($A$2:$A$500="Filled")*($I$2:$I$500={"G1","G2","G3","G4","G5","G6",2,3,4,5,6,7,8,9}))

How about,

=SUMPRODUCT(--($A$2:$A$500="Filled"),--ISNUMBER(MATCH($I$2:$I$500,B1:B14,0)))

Where B1:B14 contain G1, G2, etc....
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Why is it I NEVER remember ISNUMBER/MATCH, Brian? Am insideous internal desire to get a spanking from Aladin or you :rolleyes: You'd think I'd remember by now...
 
Master Excel Bundle

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.

Forum statistics

Threads
1,152,102
Messages
5,768,102
Members
425,454
Latest member
khoro

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
Top