Count If formula with week range

[Jeremy]

I am trying to write a count if formula to count the total in A1:A3 and count
it to what is in B4:7 in D on if the date in B1:B3 falls in the range of the
date given in D2 and on. Examples below

Thank you for the help



A B C
1 NBC 4/20/09 7865.42

2 NBC 3/28/09 1268.42

3 NBC 4/22/09 1000.00



A D E F
1 Week Start Week Start Week Start

2 3/30/09 4/13/09 4/27/09
4 NBC 1268.42 8865.42

 

[Eduardo]

Hi,
try

In D4 enter

=sumproduct(--(D3>B1:B100),--(D4<B1:B100),c1:c100)

 

[Jeremy]

Could not get it to work but keep in mind A:1 to A3 might not all be NBC so I
need it to count if it matches A4 and so on.

 

[Eduardo]

Hi,
sorry I didn';t take in consideration NBC change to

=sumproduct(--(A4="NBC"),--(D3>B1:B100),--(D4<B1:B100),c1:c100)

 

[Eduardo]

Opps, use

=sumproduct(--(A1:A3="NBC"),--(D3>B1:B3),--(D4<B1:B3),c1:c3)

 

[Jeremy]

I think we are on the correct path but am not sure you understand where I am
placing my data. Below is a better example. Sheet 1 is where the data is
and sheet two I will type in NBC or CBX and I want it to place the total in
the proper columns. The totals come from C in sheet one.


Sheet 1
a b c
1 NBC 3/16/09 15.00
2 NBC 3/30/09 30.00
3 CBX 3/31/09 15.00
4 NBC 3/29/09 10.00
5


Sheet 2
a b c d

1 Week Start Week Start Week Start
2 3/2/09 3/16/09 3/30/09
3 NBC 25.00 30.00

4 CBX 15.00
5
6
7

 

[Eduardo]

Hi,
yes the example clarify use

=sumproduct(--($A3=sheet1!$A$1:$A$100),--(D2>sheet1!$B$1:$B$100),--(D2<sheet1!$B$1:$B$100),sheet1!$c$1:$c$100)

change the range to fit your needs but remember the range has to be the same
in all parts of the formula

 

[Eduardo]

Opps I found a mistake use

=SUMPRODUCT(--($A3=Sheet1!$A$1:$A$100),--(C$2>Sheet1!$B$1:$B$100),--(D$2<Sheet1!$B$1:$B$100),Sheet1!$C$1:$C$100)

copy formula right and down

 

[Jeremy]

Well we are close but if you add a Week Start in E2 on sheet 2 for 4/6 you
get the total of each name in A in E. We are close.

 

[Eduardo]

Hi Jeremy,
The problem in column E is that you will not have anything in column F, so
in that case cut a portion of the formula as follow

=SUMPRODUCT(--($A3=Sheet1!$A$1:$A$100),--(e$2>Sheet1!$B$1:$B$100),Sheet1!$C$1:$C$100)