Formula to count cells between dates excluding duplicates

V

Vegs

I need this formula to exclude duplicate S/N which are enterd in C33:C2006
for the part "A5055"

=SUMPRODUCT(--($A$33:$A$2006>=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)),--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--($B$33:$B$2006="A5055")
 
B

Biff

Hi!

Try this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=SUM(N(FREQUENCY(IF((A33:A2006>=Date1)*(A33:A2006<Date1+7)*(B33:B2006="A5055"),MATCH(C33:C2006,C33:C2006,0)),MATCH(C33:C2006,C33:C2006,0))>0))

In order to shorten the formula just a little I used a named formula:

Date1 refers to:

=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)

Biff
 
V

Vegs

Hi Biff,

I'm coming up with an "N/A".
The "IF" function needs a "value if false".......
Thanks for your help...
 
V

Vegs

How would I change the equation below to count the S/N (excluding duplicates)
in column "C". Using the P/N in column "B". Here is the spreadsheet I'm
working with.
The formula I'm currently trying is listed after the spreadsheet.

=SUMPRODUCT(--($A$33:$A$2006>=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)),--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--($B$33:$B$2006="A5055")

Date P/N S/N
6/26/2006 A5055 1234
6/26/2006 A5055 4321
6/27/2006 A5055 1212
6/28/2006 A5055 1212
6/28/2006 A5055 2121

This is the formula I'm trying but is not working.
=SUM(N(FREQUENCY(IF((A33:A2006>=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6))*(A33:A2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7)*(B33:B2006="5055"),MATCH(C33:C2006,C33:C2006,0)),MATCH(C33:C2006,C33:C2006,0))>0)
 
B

Biff

The "IF" function needs a "value if false".......

The IF function does not need a value_if_false argument.
This is the formula I'm trying but is not working.
=SUM(N(FREQUENCY(IF((A33:A2006>=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6))*(A33:A2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7)*(B33:B2006="5055"),MATCH(C33:C2006,C33:C2006,0)),MATCH(C33:C2006,C33:C2006,0))>0)
Click to expand...

(B33:B2006="5055")

Should be:

(B33:B2006="A5055")

Based on the snippet of data you posted (not knowing what date you have
entered in C5)......

See this screencap:

http://img119.imageshack.us/img119/2631/sample6vq.jpg

Biff
 
V

Vegs

It still didn't work.

What if I forget about searching column B and just use the s/n between the
dates. Excluding the duplicates of course. I tried this formula but had no
success.

=SUMPRODUCT(--($A$33:$A$2006>=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)),--($A$33:$A$2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7),--N(FREQUENCY(C33:C2006,C33:C2006)>0))
 
B

Biff

It still didn't work.

If you looked at the screencap you would have seen that it does in fact
work.

Would you like for me to look at your file? Is that possible? If it's a huge
file I don't need the whole thing, just the sheet with this particular data.
Just let me know how to contact you.

Biff
 
V

Vegs

Your absolutely correct Biff, it does work!
Except if there are empty cells in any column, which is the case. What
changes to your formula have to be made to make it disregard empty cells?
 
D

Domenic

Biff's formula can be tweaked as follows...

=SUM(N(FREQUENCY(IF(A33:A2006>=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6),I
F(A33:A2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7,IF(B33:B2006="A505
5",IF(C33:C2006<>"",MATCH(C33:C2006,C33:C2006,0))))),ROW(A33:A2006)-ROW(A
33)+1)>0))

Also, if you download and install the free add-in Morefunc.xll, you can
use the following, more efficient, formula...

=COUNTDIFF(IF(A33:A2006>=$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6),IF(A33:
A2006<$C$5-CHOOSE(WEEKDAY($C$5),0,1,2,3,4,5,6)+7,IF(B33:B2006="A5055",IF(
C33:C2006<>"",C33:C2006)))),,FALSE)

....confirmed with CONTROL+SHIFT+ENTER. The add-in can be found at the
following link...

http://xcell05.free.fr/english/index.html

Hope this helps!
 
V

Vegs

Domenic,
I get an error located in the last part of the formula...."too few arguments
for this function" the "zero" is highlighted....
........ROW(A33:A2006)-ROW(A33)+1)>0))
 
D

Domenic

I just tested the formula and it seems fine. Did you copy the formula
from the post and paste it into you worksheet? If so, see if hard
returns have been added to the formula and remove them. Does this help?
 
V

Vegs

Sorry, Domenic...it does work correctly...I copied it again and it was
fine...I couldn't figure out why....the formula's were the same......
Thanks for your help!!
Greatly appreciated!!!
 
Top