Hello all,
I would really appreciate some help. I'm working on a worksheet that has dates in one column (column A), and numbers in another column (column B). What I'm looking to do, is look through all of column A, find all entries between a certain date (ie Nov 1 07 - Nov 30 07), and sum all the values in column B that correspond those fields, as long as the value is greater than zero or not blank.
So, for example,
A B
Nov 1 1
Nov 6 -5
Nov 3 6
Dec 6 5
Jan 1 2
I would need the formula to return 7
Any help would be really appreciated =)
=SUMPRODUCT((A1:A10>=X1)*(A1:A10<=X2)*(B1:B10>0),B1:B10)
Where X1 and X2 contain the start and end dates, respectively.
A1:B10 contain your data
edit: Changed formula to include B1:B10>0
Last edited by NBVC; 09-08-2008 at 10:25 AM. Reason: Changed formula
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Hi,
How do you get 7? The values for November total '2'.
Are the dates on column A proper Excel date numbers or just text characters which spell out 'Nov', 'Dec' etc...
Rgds
hey richard,
i got 7 from summing Nov 1 & 3 (nov 6th is negative, which i need to exclude). The date column is in date format.
NBVC, thanks for the reply, I will try this =)
Ah, OKOriginally Posted by excalibur69
I was interpreting you quite literally (and logially) when you said
I see you really meant:greater than zero or not blank.
greater than zero AND not blank.
hey guys, thanks again .. Stupid question, but how exactly to input the date in the formula? i put it in quotations as it appears on the sheet (ie "01-Nov-07"), but it wont seem to take it. Also, instead of going B1:B10, for instance, will the formula work if I just use B:B for the entire column as things will continuously be added?
Thanks again
If you want to input the "hard coded date" in the formula... then
=SUMPRODUCT((A1:A10>="01-Nov-07"+0)*(A1:A10<="01-Nov-07"+0)*(B1:B10>0),B1:B10)
With Sumproduct you have to use defined ranges, not whole column ranges.. unless you have XL2007...
To work around, you can define the range as, for example, B2:B65536
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
NBVC, you are a lifesaver. one more question if I could ask. The Sumproduct works great, but now I need to divide the figure from the sumproduct by the number of columns I got instances from, to give me an average. I would like to use the same conditions as you stated above for the count function..so for example. I would like to have =SUMPRODUCT((A1:A10>="01-Nov-07"+0)*(A1:A10<="01-Nov-07"+0)*(B1:B10>0),B1:B10)/COUNTIF((A1:A10>="01-Nov-07"+0)*(A1:A10<="01-Nov-07"+0)*(B1:B10>0),B1:B10)
I know countif is not the correct formula to use here, but any help would be really appreciated
thanks again
Last edited by excalibur69; 09-08-2008 at 12:17 PM. Reason: typo
Try:
=SUMPRODUCT((A1:A10>="01-Nov-07"+0)*(A1:A10<="01-Nov-07"+0)*(B1:B10>0),B1:B10)/SUMPRODUCT((A1:A10>="01-Nov-07"+0)*(A1:A10<="01-Nov-07"+0)*(B1:B10>0))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
works awesome! thanks again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks