# using date parameters with current formulae

##### Board Regular
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?

Alan

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### barry houdini

##### MrExcel MVP
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

##### Board Regular
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

##### MrExcel MVP
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

##### Board Regular
definitive count by dates

Thanks Barry, your a star!
Works a treat.

Kind regards

Alan

Replies
7
Views
239
Replies
17
Views
252
Replies
2
Views
110
Replies
6
Views
196
Replies
1
Views
172

1,191,371
Messages
5,986,264
Members
440,015
Latest member
knijgh

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back