# Hard One (FOR ME)

#### uber.metal

##### New Member
Here it is. I have 4 columns of information that can be entered at random on a data sheet. I also have a running statistics sheet which has formulas on it to show information from the data sheet. 3 of the columns are dates, one is a number which can be entered as any number.

I was able to make my own formula to show if some rows in column a = "date" then add up all rows that match "date" in column b.

I now need to incorporate the other 2 date fields to filter the results.

Effectively - if column a is equal to or less than "date" and column c = "NULL", then do the same as before.

Also - if column a is equal to or less than "date" and column c = "date" and column d = "NULL" then same as before.

The date has to be able to be any date and so does the number. Any help would be greatly appreciated as i was unable to find anything about this in search.

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Hi

Assuming the date is in the range A1:D9, and F1 contains the test date then

1) =SUMPRODUCT(--(A2:A9<=F1),--ISBLANK(C2:C9),(B2:B9))
2) =SUMPRODUCT(--(A2:A9<=F1),--(C2:C9=F1),--ISBLANK(D2:D9),(B2:B9))

Tony

I am sorry i should have clarified it a bit more. Basically it looks like this:

A B C D
1 11/10/2005 1 11/10/2005 12/10/2005
2 12/10/2005 4 14/10/2005
3 12/10/2005 9
4 16/10/2005 1 16/10/2005 16/10/2005
5 11/10/2005 1 11/10/2005 12/10/2005
6 13/10/2005 5 14/10/2005
7 13/10/2005 3
8 16/10/2005 1 16/10/2005 16/10/2005

For the 1st example i want the answer to show row 3 + row 7
For the 2nd example i want the answer to show row 2 + row 6

The gaps and the dates can vary. If column A has 12/10/2005 it must also add any from column B that have a lesser date in column A. Sorry about the misunderstanding.

Is this making any sense to anyone? *Bump*

Hi

Still confused. For the first result, do you want to see 12, or the text row 3 + row 7? And where do you want the output to appear? Same with the second one - exactly what result do you want, and where does it go?

Tony

Sorry, yes 12 will be the result. It is on a sheet called stats.

Hi

What cell in sheet Stats, and where is the source data? Same sheet? Or a different sheet?

Tony

Data source is same document different sheet. The cell in stats is B4.

Hi

Assuming the source data is in sheet1, range A1:D8 then

stats:
B4: =SUMPRODUCT(--ISBLANK(Sheet1!C1:C8),(Sheet1!B1:B8))
C4: =SUMPRODUCT(--ISBLANK(Sheet1!D1:D8),--NOT(ISBLANK(Sheet1!C1:C8)),(Sheet1!B1:B8))

Tony

Replies
2
Views
131
Replies
7
Views
184
Replies
2
Views
110
Replies
4
Views
94
Replies
1
Views
99

1,203,690
Messages
6,056,753
Members
444,889
Latest member
ibbara

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

### Which adblocker are you using?

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

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