Trying to count non blank cells in an array meeting one condition

**XLML09** · 06-19-2014, 08:56 AM

Hello,
I am attempting to write a formula that searches for a text string say "apples" in Column A. If it finds the string, it would count non-blank cells in Columns B-E and return that result. So if Col B-E all were non-blank, result would be "4". If only col B and col E were non-blank, result would be "2". Stuck with not being able to use CountA or DcountA to solve this.

BTW, I setup the dataset so that the text string should only appear once (ie only one row) in Col A

thanks

**Sam Capricci** · 06-19-2014, 09:00 AM

have you tried =COUNTBLANK(B2:E2)?

**ragulduy** · 06-19-2014, 09:02 AM

Assuming you are starting on row 1:
=IFERROR(COUNTA(B1:E1)*(--FIND("apples",A1)>0),"")
in F1 and copy down as necessary.

**Ron Coderre** · 06-19-2014, 09:03 AM

For Row_1...Maybe this?

Please Login or Register  to view this content.

EDITED TO INCLUDE THIS ALTERNATIVE:
If you want to count the non-blank B:E cells in the ONE ROW that contains "apples"
try this regular formula:

Please Login or Register  to view this content.

Is that something you can work with?

**martindwilson** · 06-19-2014, 09:04 AM

try
=COUNTA(INDEX(B:E,MATCH("*apples*",A:A,0),0))

**Sam Capricci** · 06-19-2014, 09:05 AM

must have missed that whole apples thing.

I kept looking at the second sentence more than the first one. <embarrassed>

**sktneer** · 06-19-2014, 09:06 AM

Another....

Please Login or Register  to view this content.

See if the attached sheet helps.

**Formula edited.

**Tony Valko** · 06-19-2014, 09:09 AM

One way...

Data Range

	A	B	C	D	E	F
1	------	------	------	------	------	------
2	Apples	X			X	2
3	Grapes		X	X	X	0
4	Pears					0
5	Apples		X	X	X	3

This formula entered in F2 and copied down:

=SUMPRODUCT((A2="Apples")*(B2:E2<>""))

**Tony Valko** · 06-19-2014, 09:13 AM

Looks like everyone has a different interpretation of the request.

It might be a good idea to post some sample data and tell us what result(s) you expect.

**sktneer** · 06-19-2014, 09:13 AM

My mistake. I counted blanks instead of non-blanks...Thanks Tony for providing the correct one.

**martindwilson** · 06-19-2014, 09:24 AM

op says

searches for a text string say "apples" in Column A

infering that all of column a should be searched
then again i may be wrong

**Tony Valko** · 06-19-2014, 09:34 AM

Originally Posted by Tony Valko

Looks like everyone has a different interpretation of the request.

It might be a good idea to post some sample data and tell us what result(s) you expect.

Let's try this...

Do you want a row by row count or do you want a single count that covers the entire range?

Trying to count non blank cells in an array meeting one condition

LinkBack

Thread Tools

Rate This Thread

Display

Trying to count non blank cells in an array meeting one condition

Re: Trying to count non blank cells in an array meeting one condition

Re: Trying to count non blank cells in an array meeting one condition

Re: Trying to count non blank cells in an array meeting one condition

Re: Trying to count non blank cells in an array meeting one condition

Re: Trying to count non blank cells in an array meeting one condition

Re: Trying to count non blank cells in an array meeting one condition

Re: Trying to count non blank cells in an array meeting one condition

Re: Trying to count non blank cells in an array meeting one condition

Re: Trying to count non blank cells in an array meeting one condition

Re: Trying to count non blank cells in an array meeting one condition

Re: Trying to count non blank cells in an array meeting one condition

Thread Information

Users Browsing this Thread

Similar Threads

how to sum cells meeting row & column condition

[SOLVED] Formula to count number of blank cells for a specific condition in another column

Count unique instances in one column meeting multiple criteria (with OR condition)

Sum all cells meeting condition

Count non-blank cells with condition

Bookmarks

Bookmarks

Posting Permissions