using date parameters with current formulae

[alsexceladmin]

Hi All,
I need to add to or amend my current formulae to help me produce a more definitive list for my boss!

I am currently using the count formulae =sumproduct(range="yes")*(range="is") to search 500 rows/cols and give me a count for six different variables. Namely - "yes" or "no" from col C, times "IS", "IB" or "IS/IB" from col D.
I have the count results set out in a table below the spreadsheet which is approx 500 rows each month.
I am now being asked, will I produce a list/table with the sort/count breakdown by weeks, using dates from the spreadsheet? These dates are entered in random order as required.

The end product would hopefully give me a count for example: YES from col A * IS from col B * (each occurence on dates say- 01/04/2006: 06/04/2006).
Then again for same but with dates 07/04/2006:12/04/2006 and so on etc.


Can anyone help me please?

Thanks in advance!

Alan

 

[barry houdini]

assuming you have your week start date in B2 and your range of dates is D1:D100 you could add this criteria to your SUMPRODUCT

=(ABS(B2-D1:D100+3)<=3)

this will give you all dates from B2 to B2+6 inclusive

 

[alsexceladmin]

Hi Barry,
Thanks for the reply, I cannot get it to work for me.
I suspect it’s because I don’t understand how the formulae operates, I am setting it up wrong!
The sample table as set up below gives me a count of instances when 2 conditions are met in cols A and C. But now I wish to add E to the equation! I’m sure that your answer will give me this provided I can set it up correctly.

In A505 I have entered:
=SUMPRODUCT(A2:A500=”YES”)*(C2:C500=”IS”)*(ABS(A505-E2:E500+3)<=3)
This returns VALUE in the cell.
In A506 to A510 I have entered:
=SUMPRODUCT(A2:A500=”NO”)*(C2:C500=”IS”) then (YES IB) then (NO IB) and so on depending – as shown in col’s “C” and “E”. These give correct returns as shown, but only for
Full 500 row range for the month (rather than by week) which is what they now want.
I can set up Print ready returns boxes to suit each weekly cycle, but of course it’s the formulae set up where I’m going wrong! I think?
Can you help guide me?

A C E
ATT Y/N BEN DATE
2 YES IS 01/04/2006
3 YES IS 07/04/2006
4 YES IB 03/04/2006
5 NO IS/IB 04/04/2006
6 NO IS/IB 05/04/2006
7 YES IB 06/04/2006
8 YES IS/IB 11/04/2006
9 NO IS 09/04/2006
10 NO IS 09/04/2006
11 NO IS 10/04/2006
12 NO IS 15/04/2006
13 NO IS 08/04/2006
14 YES IS 13/04/2006
15 YES IS 03/04/2006
A C E
505 #VALUE! YES IS
506 5 NO IS
507 2 YES IB
508 0 NO IB
509 1 YES IS-IB
510 2 NO IS-IB
511 #VALUE!


Thanks again

Alan

NB I would have posted using HTML but I keep getting You may have to manually set up reference to the (VBIDE=VBE6EXT.OLB) whatever that means. It’s way over my head.

 

[barry houdini]

You need to have a cell with the start date of the week, so if A505 contains the date 01/04/06 then this formula in B505

=SUMPRODUCT((A$2:A$500=”YES”)*(C$2:C$500=”IS”)*(ABS(A505-E$2:E$500+3)<=3))

will give you a count for the week 01/04/06 to 01/07/06 inclusive

 

[alsexceladmin]

definitive count by dates

Thanks Barry, your a star!
Works a treat.

Kind regards

Alan