LinkBack URL
About LinkBacks
I am trying to input a formula on the Payments worksheet that will go to the Jobs worksheet and total up dollars in column BF for a certain date range (something like 09/08/11 - 09/14/11) that is in column K.
Thank you so much!
I am trying to input a formula on the Payments worksheet that will go to the Jobs worksheet and total up dollars in column BF for a certain date range (something like 09/08/11 - 09/14/11) that is in column K.
Thank you so much!
Are you working off of Excel 2003 or 2007?
Going for Guru! Click the Star to the bottom left of this post if I helped!
I am working off of 2007 and 2010 as it depends on which computer I am working on at the time. Thank you
=Sumifs(BF:BF,K:K,">40794",K;K,"<40800")
40794 and 40800 are the numerical equivalents of the dates that you provided... I'm not sure if they'll work if you use the date format in that equation
Probably best to put the dates in cells, e.g. with start date in Y2 and end date in Z2
=SUMIFS(Jobs!BF:BF,Jobs!K:K,">="&Y2,Jobs!K;K,"<="Z2")
Audere est facere
Hi,
Here are two ways to do this problem attached.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
I input the following:
=SUMIFS(Jobs!BF:BF,Jobs!K:K,">='Payments'&09/08/2011,Jobs!K;K,"<="09/14/2011")
and received 0 as a value. Can you please tell me what I am doing wrong?
Look at my example where it works.
The problem on your formula is the quotes around the dates on the second criteria and not the first. You might need to use the # sign to tell they are dates. See the example I gave in the below attached.
Personally I prefer dates in cells, then it's
1) easier to copy the formula for multiple date ranges
2) easier to alter the date ranges without touching the formula
3) easy to see which date ranges you are using without having to examine the formula
...but if you want them in the formula you can use a version like this
=SUMIFS(Jobs!BF:BF,Jobs!K:K,">=09/08/2011",Jobs!K;K,"<=09/14/2011")
The dates will be evaluated according to your regional settings so that formula wouldn't work for me in the UK because 09/14/2011 isn't a valid date in dd/mm/yyyy format.
For unambiguous dates in the formula I would use DATE function, so that would be
=SUMIFS(Jobs!BF:BF,Jobs!K:K,">="&DATE(2011,9,8),Jobs!K;K,"<=&DATE(2011,9,14))
Thank you all for the great information. I have been trying this and have just realized that in column BF which is my dollars, it is a formula which is =SUBTOTAL(9,AT769:BA769,BC769)
Can you please tell me if I would be able to somehow incorporate this into the formula? Thank you all again!!!!
Whether BF has a formula or just a value shouldn't make any difference to your SUMIF formula, that column should be "summable" either way...isn't the SUMIF working?
WOO HOO!!!!! I just got it to work!! I just reworked the formula and it now works. I must have been doing something incorrect. Thank you again so much!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks