Can I use "Count If" function with noncontiguous s

[bullocks6]

Can the "Count If" function be used with a noncontiguous range(set) of cells?

For example,

I want H1 to display the number of "X" that are found in A1, C1, and E1.

I can get the the Count If function to work fine with the range A1:E1.

However for this project I can't allow the "X" in B1 and D1 to be counted.

Thanks for any suggestions.

Allen Bullock

 

[Aladin Akyurek]

Re: Can I use "Count If" function with noncontiguo

It looks like you want to count "X" in every 2nd cell in a horizontal range. If so:

=SUMPRODUCT((MOD(COLUMN($A$1:$E$1)-CELL("Col",$A$1)+0,2)=0)*($A$1:$E$1="X"))

Given the fact you have a small set of cells, you could also use:

=(A1="X")+(C1="X")+(E1="X")

 

[Brian from Maui]

Re: Can I use "Count If" function with noncontiguo

Can the "Count If" function be used with a noncontiguous range(set) of cells?

For example,

I want H1 to display the number of "X" that are found in A1, C1, and E1.

I can get the the Count If function to work fine with the range A1:E1.

However for this project I can't allow the "X" in B1 and D1 to be counted.

Thanks for any suggestions.

Allen Bullock

Just in case,

http://www.mrexcel.com/board2/viewtopic.php?t=29977&postdays=0&postorder=asc&start=10

 

[bullocks6]

Re: Can I use "Count If" function with noncontiguo

Aladin,

Thank you so much. I think I will go with your second suggestion
(=(A1="X")+(C1="X")+(E1="X") since it seems a little "simpler" and I will never be pulling info from more than 5 or 6 cells at a time. I had been beating my head against the wall for 2 days trying to get the Count If function to work for me.

I thank you and the teachers at my school thank you. It will mean one less column for them to fill in. This is for a spreadsheet in which the teachers fill in student performance info.

Brian,

Thank you for the link to the other posts related to my question. I enjoyed seeing the different approaches to a similar question.

Allen