#### devans

##### New Member
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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

##### MrExcel MVP
=SUMPRODUCT(--(RangeInA="Filled"),--(RangeInI>=10),--(RangeInI<=17))

#### just_jon

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

#### just_jon

##### Legend
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
IT WORKED. modified this one for another formula count text based on what is in another column. MANY THANKS!!!

#### Brian from Maui

##### MrExcel MVP
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}))

=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
Why is it I NEVER remember ISNUMBER/MATCH, Brian? Am insideous internal desire to get a spanking from Aladin or you You'd think I'd remember by now...

Replies
21
Views
163
Replies
3
Views
205
Replies
1
Views
92
Replies
0
Views
185
Replies
12
Views
131

1,187,179
Messages
5,962,057
Members
438,578
Latest member
MrJimC

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

### Which adblocker are you using?

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

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