PDA

View Full Version : Solved: What formula or vba code for this case



slamet Harto
05-13-2009, 05:48 AM
Hi there,

please help me on how to count APPOINTMENTDATE (see in sheet 1 -D Column)

for example in sheet1
Username is "AA"
Date 20090408
No of Call is 5, because there are 5 data for the date
Count of ApptDate is 5 ' What formula we use to count it

Example 2
UserName is "OO"
Date 20090406
no of call is 13
Count of Appt Date should be 4

Please find the attached for your reference.

Thanks for assistance.

Hoopsah
05-13-2009, 06:04 AM
Hi

can I just check I am getting this right.

If the User Name AA has data in the Appt Date cell it would count 1 otherwise don't count it?

Just that I ran a formula and I was getting different results to you, you have said column D should read: 5,5,5,4,0 Total 19

I get 5,10,10,4,10 Total 39

Maybe I'm missing something.

slamet Harto
05-13-2009, 06:09 AM
Hi

can I just check I am getting this right.

If the User Name AA has data in the Appt Date cell it would count 1 otherwise don't count it?


Yes, you are right.



Just that I ran a formula and I was getting different results to you, you have said column D should read: 5,5,5,4,0 Total 19

I get 5,10,10,4,10 Total 39


I just test with autofilter and count it manualy. you can see in the next sheet

Thanks for reply.

slamet Harto
05-13-2009, 06:15 AM
Thanks for that. I got the answer from you

=SUMPRODUCT(--($A$9:$A$60=$A2)*(B2=$B$9:$B$60),--($E$9:$E$60>0))

Once again thank you,
Regards, harto

Hoopsah
05-13-2009, 06:19 AM
Ahh - sorry didn't realise it was date specific. I have amended the formula (=SUMPRODUCT(--($A$9:$A$60=$A2),--($B$9:$B$60=$B2),--($E$9:$E$60>0)))

and attached a copy of the worksheet with column D completed.

Hoopsah
05-13-2009, 06:20 AM
Ahh - sorry didn't realise it was date specific. I have amended the formula
(=SUMPRODUCT(--($A$9:$A$60=$A2),--($B$9:$B$60=$B2),--($E$9:$E$60>0)))

and attached a copy of the worksheet with column D completed.