Count array only if criteria for all range is met

[Hammett]

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

 

[etaf]

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)

 

[oldbrewer]

	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.

 

[Peter_SSs]

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.

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
	A	B	C	D	E	F	G	H
1	Customer/Week	1	2	3	4	5	6	7
2	Customer 1				4		2
3	Customer 2		5	1		6		7
4	Customer 3				2
5	Customer 4				6
6	Customer 5	2	1		5		4	8
7	Customer 6			7		8	1
8	Customer 7					1
9	Customer 8					2	2	2
10	Customer 9							1
11
12	Count of New Contacts Each week	1	1	1	3	2	0	1
Count new contacts

 

[Aladin Akyurek]

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:

=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)