Hello,
I'm having problem with count function. I would like to count how many "sets" of text before blank coulmn is there in one row.
In example: In row 1 there is text "SD" in columns A, B, C and then D is blank and then in E, F, G there is text "LD". So count or some other function should return number 2 because there are two sets of text - A,B,C and D,F,G
Does anyone have some idea?
Thank you and have a great day.
Pls attach a sample excel file with desired result with different examples
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
I dont know why, but I cannot attach file, so I hope this table givs you an idea of what I'm looking for - numbers in last column, which represent how many sets of text, before there is a blank cell.
SD SD SD SD SD 2 SD SD SD SD SD SD 3 0 SD SD SD SD 4 SD SD SD SD SD 1 SD SD SD SD SD 3
Last edited by Lynx2x; 01-31-2017 at 07:21 AM.
Try this...
Data Range
This array formula** entered in I1 and copied down:
=SUM(IF(FREQUENCY(IF(A1:H1<>"",COLUMN(A1:H1)),IF(A1:H1="",COLUMN(A1:H1))),1))
** 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.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thank you Tony Valko!! It works like a charmAlso thanks to all the other guys that helped!
You're welcome. Thanks for the feedback!
Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
assuming that your data start in A1, you could use tis array formula:
=SUM(IF(FREQUENCY(IF(A1:I1="SD",COLUMN(A1:I1)),IF(A1:I1< > "SD",COLUMN(A1:I1))),1))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Who spotted my "deliberate" mistake???
=SUM(IF(FREQUENCY(IF(A1:H1="SD",COLUMN(A1:H1)),IF(A1:H1< > "SD",COLUMN(A1:H1))),1))
I've looked at your solution and it works very good, but is it possible that function is not looking just for "SD", but any text? So insted of "SD" there could be "LD" or "SUNNYDAY" or whatever?
Thank you for your very good and quick answer
Using Glenn's formula , try
=SUM(IF(FREQUENCY(IF(A1:H1=M1,COLUMN(A1:H1)),IF(A1:H1<>M1,COLUMN(A1:H1))),1))
where M1 (or cell of your choice) contains search value "SD", "LD", etc
You can use COUNTIFS function:
=COUNTIFS(A1:H1,"SD",B1:I1,"<>SD")
John...that would be ok if in one row there would be only one type of text SD or LD,... But what if in one row there are different type of texts...
Something like that:
SD SD SD LD MM 2
Phuocam....that would be a lot of countifs....I'm looking for function that is looking for any text....
for any text....
=COUNTIFS(A1:H1,"?*",B1:I1,"")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks