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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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}
 
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0
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.....
 
Upvote 0
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}))
 
Upvote 0
IT WORKED. modified this one for another formula count text based on what is in another column. MANY THANKS!!! :biggrin:
 
Upvote 0
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....
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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