Formula to count how many adjacent cells have value that precede specified cell

L

lisburl

New Member
Joined
Apr 7, 2018
Messages
8
Good afternoon!

I need help with a formula that will give me the total the number of cells in a row that contain a value that are adjacent to a target cell (H).

For example: Column H would total the following
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="bgcolor: #fff2cc"]H TOTAL[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #fff2cc"]1[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #fff2cc"]1[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #fff2cc"]1[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #fff2cc"]0[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #fff2cc"]2[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #fff2cc"]1[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #fff2cc"]1[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #fff2cc"]3[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #fff2cc"]0[/TD]
[/TR]
</tbody>[/TABLE]


Thank you for any help.
 
Sort by date Sort by votes
I figured out why this formula did not work for me at first. I had added an additional so my data had shifted from C to H.

It works now that I updated it to reflect the correct columns. I have run into a challenge though because occasionally the vlookups are returning empty cells. This does not affect the outcome if the last column is a 0, but if it's a 1 it is adding the blank cells as 1s. Is there any way around this?

BTW - your formula has saved me sooooo much time!! Thank you!!!
 
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
lisburl said:
I figured out why this formula did not work for me at first. I had added an additional so my data had shifted from C to H.

It works now that I updated it to reflect the correct columns. I have run into a challenge though because occasionally the vlookups are returning empty cells. This does not affect the outcome if the last column is a 0, but if it's a 1 it is adding the blank cells as 1s. Is there any way around this?

BTW - your formula has saved me sooooo much time!! Thank you!!!
Click to expand...

Hi!

I'm happy that my suggestion help you.

Try the new version below:

=COLUMNS(B2:G2)-IFERROR(MATCH(2,INDEX(1/((B2:G2=0)+(B2:G2="")),)),0)

Markmzz
 
Upvote 0
New issue popped up - We are collecting data for more than six months now, and I'd like to extend the formula. So instead of B:G I need it to go from B:M. The issue is that data is uploaded at different times and the blank cells that precede the formula cell make the formula result in a 0. (If the blank cells precede cells that have a 1 or 0 the sum is not affected).

For instance:Everything works if the formula ends at G: =COLUMNS(B2:G2)-IFERROR(MATCH(2,INDEX(1/((B2:G2=0)+(B2:G2="")),)),0)
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=daeef3]#daeef3[/URL] , align: right"]N[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=daeef3]#daeef3[/URL] , align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

It doesn't work if the formulat ends at M: =COLUMNS(B2:M2)-IFERROR(MATCH(2,INDEX(1/((B2:M2=0)+(B2:M2="")),)),0)<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=daeef3]#daeef3[/URL] , align: right"][/TD]
[/TR]
</tbody>[/TABLE]



Any suggestions?
Thank you!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Copy only Value not Formula to new Cell location after ActiveX Macro button pushed.
Replies
2
Views
168
VinnyDoesntKnowExcelCode
V
E
  • Solved
Identify product that have different values with a spilled formula
Replies
3
Views
84
ED38
E
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
392
Asbestos_Jen
Asbestos_Jen
Lacan
  • Question Question
How to filtered/extract names and calculate difference between values?
2
Replies
16
Views
252
PeteWright
PeteWright
C
  • Question Question
Formula not working to show total sales per day, #VALUE! Can't find the issue (Ignore Conditionals) I3 works fine, J3&J4 are what I have tried.
2
Replies
15
Views
599
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,022
Latest member
RobertV1609

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top