Hard One (FOR ME)

[uber.metal]

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.

 

[acw]

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

 

[uber.metal]

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.

 

[uber.metal]

Is this making any sense to anyone? *Bump*

 

[acw]

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

 

[uber.metal]

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

 

[acw]

Hi

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


Tony

 

[uber.metal]

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

 

[acw]

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