Count If formula with week range

J

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
 
J

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.
 
E

Eduardo

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

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

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
 
E

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
 
E

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
 
J

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.
 
E

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top