Hard One (FOR ME)

uber.metal

New Member
Joined
Nov 21, 2005
Messages
5
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.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

New Member
Joined
Nov 21, 2005
Messages
5
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.
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814

ADVERTISEMENT

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
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814

ADVERTISEMENT

Hi

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


Tony
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,711
Members
414,401
Latest member
grenona2020

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?

Disable AdBlock

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
Top