Count the non blank cells in a row occurring after a ">0" value between two dates

[ashisha155]

I have been provided the daily sales data to track the post price revision performance of a product in different areas with different launch dates, now I've to calculate the no. of visits of the salesperson after the launch, that means I want to count the non blank cells occurring after the ">0" value in a row between two given dates:

Salesperson 1-Aug 2-Aug 3-Aug 4-Aug 5-Aug 6-Aug LaunchDate Result
A 0 10 0 5 0 12 3-Aug 2
B 0 20 0 2-Aug 1
C 4 0 5 0 0 4-Aug 0
D 10 0 4 1-Aug 0

 

[venkat1926]

your data as I visualise is like this

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Salesperson	1-Aug	2-Aug	3-Aug	4-Aug	5-Aug	6-Aug	LaunchDate	Result
2	A	0	10	0	5	0	12	3-Aug	2
3	B	0	20	0				2-Aug	1
4	C	4	0	5	0	0		4-Aug	0
5	D	10	0	4				1-Aug	0
Sheet1

SALESLPERSON A
laluch date 3 aug. 4 aug and 6 aug are non blanks so result 2 ok

B
launch date 2 aug. there are no non blanks after 2 aug so it should be 0 how is it 1

C launch date 4 aug. there are no non blanks after 4 aug so 0 ok

D launch data 1 aug. 3 aug is non blank so it sholud be 1 how is it 0.

please explain.logic

 

[ashisha155]

Thanks a lot venkat for your interest I'm helping me out.
I need to count the non blanks cells which include the zero values since it counts the sales person's visit after the first launch be the repeat sales happen or not.

 

[venkat1926]

still confusion in my mind
salesman A

launch date 3 aug.


4th aug is 5
5th aug 0
6th aug 12

then it should be 3 not 2

explain with this and also each salesman as given by me above

 

[ashisha155]

still confusion in my mind
salesman A

launch date 3 aug.


4th aug is 5
5th aug 0
6th aug 12

then it should be 3 not 2

explain with this and also each salesman as given by me above

For salesperson A launch date is 3-Aug however his launch sales happen on 4-Aug with sales of 5 units after the launch he visited the outlet on 5-Aug with no sale and his second visit was on 6-Aug with sale of 12 units, thus "after launch" his total visits are 2 only
I the above table the result for salesperson D is wrong it should be 2, apologies for the same.
Cheers!

 

[venkat1926]

"For salesperson A launch date is 3-Aug however his launch sales happen on 4-Aug with sales of 5 units after the launch he visited the outlet on 5-Aug with no sale and his second visit was on 6-Aug with sale of 12 units, thus "after launch" his total visits are 2 only"

still confused.
according to above quote the number you require is
NUMBER OF SUCCESSFUL VISITS from the next day to launch date

that is why

salesperson A
launch date 3 aug
from 4 th to 6 successful visits are only 2 that is 4th and 6th ok
salesperson B
launch date 2 aug
between 3 aug and 6 aug no successful visit so it must be 0
salesperson C
launch date 4th aug
between 5th and 6th aug no successful visit so must be 0
salesperson D
launch date 1 august
between 2 and 6th aug there are only one successful visit that is 3 aug so it must be 1

on the other hand criterion is no. of visits with successful and unsuccessful visits then
sales person A 4th,5th 6th so must be 3
sales person B 3 aug so must be 2
sales person C 5th aug so must be 1
sales person D 2nd and 3rd so 2

your comments please. my macro is almost ready only needs tweaking on the basis of your comments.

 

[ashisha155]

Hi Venkat

Though the launch dates are officially decided but the actual launch will happen with >0 value date which falls after the official launch date.
After that successful launch i.e. >0 value I need to count all the successful (>0) and unsuccessful (=0) visit.

Salesperson A: Official launch date is 3-Aug, actual launch happen on 4-Aug, after that launch he visits the outlet on 5th unsuccessful and on 6th successful so it is 2.

Salesperson B:Official launch date is 2-Aug, actual launch happen on 2-Aug, after that launch he visits the outlet on 3-Aug unsuccessful so it is 1.

Salesperson C:Official launch date is 4-Aug, actual launch did not happen (no >0 value after 4-Aug so it is 0.

Salesperson D:Official launch date is 1-Aug, actual launch happen on 1-Aug, after that launch he visits the outlet on 2-Aug unsuccessful and 3-Aug successful so it is 2.(I had made an error in calculating this Salesperson D, apoligies for it)

 

[venkat1926]

now understood. but see saleslperson D
launch date 1 aug
effective launch date first non zero visit after 1st aug that is 3 aug.
no other visits after 3 aug
so it must be 1 not 2
reconsider.

LANGUAGE IS A POOR TOOL OF COMMUNICATION.WHEN PERSONS ARE NOT FACE TO FACE.

 

[ashisha155]

Salesperson D: After launch on 1-Aug his first visit was on 2-Aug(unsuccessful) denoted as '0' , his second visit was on 3-Aug (successful) thus it is 2.

 

[venkat1926]

sorry again

"the actual launch will happen with >0 value date which falls after the official launch date."

that is actual launch date is the first SUCCESSFUL date

but now yous say

"After launch on 1-Aug his first visit was on 2-Aug(unsuccessful) denoted as '0' , his second visit was on 3-Aug"

that means unsuccessful date is taken as the actual date