Column count help

[Ronderbecke]

Currently I am having trouble getting a count of the columns needed. I am not sure why the formula I am working on is not doing the trick and figure it might have something to do with how this data is entered? Currently, its pulling data from individual sheets. The data is either a date, a date that has a link inserted to it, "none", or its blank. I want to only calculate the cells that have the date or the date with a link. The formula structured was this:

=SUMPRODUCT(IF(MOD(Column(H12:FB)-Column(H12),7)=0,IF(ISTEXT(""),H12:FB<>0)))

but its not working. It works if I do it the long way, but with constantly adding more, it would be way easier if it calculated? Here is what I have to do to get correct stats:

=COUNTA(H12:H80,R12:R80,AB12:AB80,AL12:AL80,AV12:AV80,DD12:DD80,CT12:CT80,DN12:DN80,DX12:DX80,ER12:ER80,FB12:FB80)-counta(IF(H12:H80,R12:R80,AB12:AB80,AL12:AL80,AV12:AV80,DD12:DD80,CT12:CT80,DN12:DN80,DX12:DX80,ER12:ER80,FB12:FB80),"NONE")

Id like to just start on H12 going down the full column and count every 7 columns.

 

[edmitchell]

Hi, could you clarify what you mean by a 'date with a link', do you mean that a cell contains a date but it is 'also' a hyperlink?

 

[Aladin Akyurek]

Does this deliver what you have in mind?

=SUMPRODUCT(--(MOD(COLUMN(H12:FB)-COLUMN(H12),7)=0),--(LEN(H12:FB)>0),ISTEXT(H12:FB)+ISNUMBER(H12:FB))

 

[stunnrock]

But they are not 7 columns apart... Most are 10 columns apart, there is a gap of 50 columns (between AV and CT), and another of 20 columns (between DX and ER).

 

[Ronderbecke]

Hi, could you clarify what you mean by a 'date with a link', do you mean that a cell contains a date but it is 'also' a hyperlink?

Correct. I simply inserted hyperlink to the date so a picture of the receipt is attached.

 

[Ronderbecke]

But they are not 7 columns apart... Most are 10 columns apart, there is a gap of 50 columns (between AV and CT), and another of 20 columns (between DX and ER).

This is the reason that I need the formula because I simply havent added the columns yet after adding new groups. Its a 10 column addition every time we add a new rep so I have to manually add those columns each time.

 

[Ronderbecke]

Does this deliver what you have in mind?

=SUMPRODUCT(--(MOD(COLUMN(H12:FB)-COLUMN(H12),7)=0),--(LEN(H12:FB)>0),ISTEXT(H12:FB)+ISNUMBER(H12:FB))

Aladin, thanks so much but its giving me an error message?:

ErrorSUMPRODUCT has mismatched range sizes. Expected row count: 1. column count: 151. Actual row count: 989, column count: 151.

Does that make sense to you?

 

[Aladin Akyurek]

Are the columns of interest every 7th columns?

 

[Ronderbecke]

Are the columns of interest every 7th columns?

They Are. Here is a sample clip:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Travis Jones Customers:

Install Date:

W H

H V A C

EE Kit Done:

Whose Kit?

Rebate Done:

Anders Jacobson Customers:

Install Date:

W H

H V A C

EE Kit Done:

Whose Kit?

Rebate Done:

Kevin Nordeen Customers:

Install Date:

W H

H V A C

EE Kit Done:

Whose Kit?

Rebate Done:

Douglas Rogers

11/22/2017

12/15/2017

Dividend

Carlton Atkinson

Harbor

Michael Martin

9/14/2017

11/7/2017

Dividend

9/21/2017

Brigid Creeden

12/15/2017

Harbor

Teresa Cappelluccio

10/6/2017

12/3/2017

Harbor

10/26/2017

Merma Jagoda

9/14/2017

10/6/2017

Dividend

9/21/2017

<colgroup><col style="width: 60px"><col width="60"><col width="60"><col width="52"><col width="76"><col width="21"><col width="22"><col width="76"><col width="58"><col width="76"><col width="60"><col width="60"><col width="60"><col width="60"><col width="74"><col width="21"><col width="21"><col width="76"><col width="58"><col width="76"><col width="60"><col width="60"><col width="60"><col width="60"><col width="71"><col width="21"><col width="19"><col width="68"><col width="58"><col width="69"></colgroup><tbody>
</tbody>

The EE kits need to be tracked, and i'll do the same formula for rebates as well.

 

[Aladin Akyurek]

You need to clarify this sample: What is the cell of "Travis Jones Customers:"? What is the cell of "Install Date:", etc.?