date error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi
i have 2 problem.
1) iam using a vlookup formula, but when there is no value in the cell the
cell shows the ( 0 for date and jan for month) 0-jan. but i want to be 0
instead of the 0-jan, why its happening,

2) i have a database upto 2000 subscriber paying monthly sub. some customer
pays twice in month. i used vlookup formula . it shows only one date , is
there possible to show all the date in one cell, so i can know how many times
that customer paid the subs in these month. if instead if vlookup i used the
sumif formula its add the date and i got the wrong date.

so pls help.
regards
anil
 
1-
Try something like IF(VLOOKUP(....)=0;"";VLOOKUP(....))

2-
I don't know if you can show all the dates in one cell but you can identify
the customer that pays twice using SUMPRODUCT. Something like this:

=SUMPRODUCT(--($A$5:$A$104=$A6);--($B$5:$B$104=$B6))>1
where column A has the month and column B has the name of customer and you
can put this formula in C6. It will show if the customer in B6 paid twice in
the same month of A6
 
Back
Top