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.
 

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

Forum statistics

Threads
1,078,473
Messages
5,340,543
Members
399,383
Latest member
rahmanab001

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top