Count if one number of a concatenated cell is greater than 0

[te31]

Hi,

I'm building a tool that has a column that concatenates funding applications as such:

x pending; y approved; z denied (with x,y,z representing the real number values)

This is replicated in hundreds of rows, but I need a way to count the unique number of rows, whose concatenated cell has a y value greater than 0. Is this possible?

This is the formula in the concatenated cell:

Please Login or Register  to view this content.

Any help would be so truly appreciated!

Thanks!

[XOR LX]

Hi,

Forgive me if I've misunderstood, but why would the count of rows whose concatenated cell has a y value greater than 0 be different from the count of rows whose count of "Approved" is greater than 0?

Regards

[abousetta]

Assuming your first concatenation is in Cell A1, put this formula in another cell.

Please Login or Register  to view this content.

Change 'A1' to your needs.

abousetta

[te31]

Oh sorry, I should have explained. The cell is concatenating values from about 50 different cells in the same row, so it is counting the number of applicants in that row (row 5) that have been approved/pending/denied, from cells that are further down the row. This would result in that cell putting out a value like:

14 Pending; 18 Approved; 9 Denied

So if there is a way to count the number of rows that contain at least one 'Approved' then that would work too. Hope that makes sense; I can't put a sample up because it's confidential but I also can't seem to make the formula work on a different document.

[Jonmo1]

so you need to count the range where your concatenate formula gets it's Approved results from for >0

I think you need to post a sample book.

[XOR LX]

Perhaps, assuming your range extends from M5:BX55, this array formula:

=SUM(--(MMULT((--($M$5:$BX$55="Approved")),TRANSPOSE(COLUMN($M$5:$BX$55)^0))>0))

Regards

[te31]

OK so here is a sample. I need to count the total number of rows whose cell in the B column has an approved number >0. I think the other formulas provided on this thread work really well for individual rows, but not as a summing/counting function.

[XOR LX]

Did you try the formula I posted? Works fine for me.

Here you simply change the range reference:

=SUM(--(MMULT((--($C$5:$E$17="Approved")),TRANSPOSE(COLUMN($C$5:$E$17)^0))>0))

There's no need to do any complicated in-string searching of the concatenated cells.

Regards

[te31]

Hey XOR,

I just copy pasted that formula into the sample worksheet, but it comes up with a value error.

[XOR LX]

Please re-read post #6.

You're not the first (and certainly won't be the last!) to miss the bit about about this being an array formula.

(I used to have a footnote in large, emboldened fonts in such cases, but people still always missed it!! So now I just settle for emboldening a single word and trust that people will realise it's important. Apologies, since this also is clearly not working!)

Regards

[te31]

Oops I'm so sorry for that. Yes it works. That must get annoying. Thank you!!

[XOR LX]

I just need to come up with a new strategy to get people's attention (and without going too over-the-top!)

And you're welcome!

[sktneer]

Another simple way to do this is by creating a helper column. Please see the attached sheet.

[Tony Valko]

I have fairly good success using this template:

This array formula** ....

Some array formula here

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

The array enter explanation is one of my "canned replies" so I don't have to type or copy/paste it every time.

I think you need so many posts/rep points to get canned replies. If you have them they're located at:

User CP, in left side pane, all the way at the bottom in Miscellaneous>Your Canned Replies.

You can have up to 8 personal canned replies. Nice feature especially if you're a "gimp typer" like me!

[XOR LX]

Thanks, Tony. Appreciated.

Looks like I don't yet have the privilege, though!! Perhaps in the meantime I could just pinch yours and paste it in everytime?

Cheers

[Tony Valko]

Perhaps in the meantime I could just pinch yours and paste it in everytime?

Absolutely.

And, for those occasions when the message doesn't work, I use this more detailed message as a follow up:

Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you *must* use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo to produce them. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo.

[XOR LX]

Yes, I would find it hard to believe that anyone could come back to you with a "What's an array formula?"-type question after seeing that!

I think I may have to do something as it's actually becoming something of an issue. Not that I don't mind explaining it (again and again) but it makes sense if you can at least reduce the number of occasions on which you have to do so.

And I'll maybe chase the admin team to see if I'm getting close to that magic 'automatic feedback' status yet!

Thanks a lot

[Tony Valko]

You're welcome!