# Count array only if criteria for all range is met

#### Hammett

##### New Member
Hi all,

I am having troubles to count and array. It has only 1 criteria, but the range to count is big. I think it will be best if I show you an example:

 Customer/Week 1 2 3 4 5 6 7 8 9 10 11 12 12 14 Customer 1 1 3 4 9 12 17 5 1 Customer 2 5 1 6 7 10 1 3 4 Customer 3 2 1 5 7 8 15 4 8 1 Customer 4 7 8 1 9 1 5 8 1

<tbody>
</tbody>

This table represents how many times a customer has been contacted during a specific week. So, for example, in week 1 I contacted 2 customers (Customer 1 and Customer 3). Next week I want to know how many NEW customers I have contacted (Customer 2). On week 3 I will need to count Customers I have not contacted on week 1 nor week 2 (Customer 4) and so on.

The actual list of Customer is very big, and I have come with this formula that works OK for comparing if the previous week I have contated or not.

{SUM(ISBLANK(\$AE\$4:\$AE\$116)*(AF4:AF116>0))} where AE4:AE116 is week 1 and AF4:AF116 is week 2.

Since I have to repeat this for every week, I can keep nesting the ISBLANK inside the formula, but doing it 52 times is going to be mad.

I have tried setting a broader range for the ISBLANK part, but this will not work propperly.

So, at the end, I will need to see in week 14 the count of customers that have not been contacted in the 13 weeks before.

Hope this make sense.

Thanks very much

Kind regards,

Jordi

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
can you simply sum the rows and use an IF

=IF( SUM(\$A1:A1)>0, "contacted", "Not Contacted)
copy across and down the spreadsheet
then as you enter the numbers (overwriting the formula each week)

 contacts in previous 13 weeks Customer/Week 1 2 3 4 5 6 7 8 9 10 11 12 13 14 52 Customer 1 1 3 4 9 12 17 5 1 33 Customer 2 5 1 6 7 10 1 3 4 51 Customer 3 2 1 5 7 8 15 4 8 1 39 Customer 4 7 8 1 9 1 5 8 1 0 Customer 5

<colgroup><col span="2"><col><col span="14"></colgroup><tbody>
</tbody>
Simply totalling visits in the 13 weeks will show if someone has not been contacted.

Jordi, welcome to the MrExcel board!

Here's one way, as I understand your request. It uses a user-defined function (UDF). To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below and copy across.

You mentioned that you had a "very big" list which may mean this code becomes sluggish.
If that happens post back and tell us how "big" your list is, roughly.

Code:
``````Function CountNew(rng As Range) As Long
Dim r As Long, rws As Long, cols As Long, x As Long

With rng
rws = .Rows.Count
cols = .Columns.Count
For r = 1 To rws
If .Cells(r, cols).Value > 0 _
And WorksheetFunction.Count(.Rows(r)) = 1 Then
x = x + 1
End If
Next r
End With
CountNew = x
End Function``````

Excel Workbook
ABCDEFGH
1Customer/Week1234567
2Customer 142
3Customer 25167
4Customer 32
5Customer 46
6Customer 521548
7Customer 6781
8Customer 71
9Customer 8222
10Customer 91
11
12Count of New Contacts Each week1113201
Count new contacts

 Customer/Week 1 2 3 4 5 6 7 8 9 10 11 12 13 14 Customer 1 1 3 4 9 12 17 5 1 Customer 2 5 1 6 7 10 1 3 4 Customer 3 2 1 5 7 8 15 4 8 1 Customer 4 7 8 1 9 1 5 8 1 Distinct Count New Customers 2 1 1 0 0 0 0 0 0 0 0 0 0 0

<tbody>
</tbody>

B6, control+shift+enter, not just enter, and copy across:
Rich (BB code):
``````=SUM(IF(FREQUENCY(IF(\$B\$2:B5<>"",
IF(ISNUMBER(\$B\$2:B5),MATCH(\$A\$2:\$A\$5,\$A\$2:\$A\$5,0))),
ROW(\$A\$2:\$A\$6)-ROW(\$A\$2)+1),1))-SUM(\$A\$6:A6)``````

Replies
4
Views
150
Replies
13
Views
199
Replies
12
Views
225
Replies
3
Views
222
Replies
1
Views
101

1,203,379
Messages
6,055,096
Members
444,761
Latest member
lneuberger

### 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.

### Which adblocker are you using?

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

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