L
LWilson
Is it possible to have a sum in a Vlookup?
T
T. Valko
See if this is what you had in mind...
...........A..........B
1........x...........3
2........z...........4
3........x...........1
4........x...........4
5........y...........2
Sum column B where column A = x:
=SUMIF(A1:A5,"x",B1:B5)
...........A..........B
1........x...........3
2........z...........4
3........x...........1
4........x...........4
5........y...........2
Sum column B where column A = x:
=SUMIF(A1:A5,"x",B1:B5)
L
LWilson
Can I send you a sample of the file and show you what I'm trying to
accomplish? There is too much to be able to explain here and I think an
actual illustration might help.
Thanks!
accomplish? There is too much to be able to explain here and I think an
actual illustration might help.
Thanks!
T
T. Valko
How do I know you won't send me a nasty virus?
What you could do is use any of several free file hosting sites. You'd
upload your file to the file host site then post a link to that file. Anyone
interested could then download the file to look at it.
What you could do is use any of several free file hosting sites. You'd
upload your file to the file host site then post a link to that file. Anyone
interested could then download the file to look at it.
L
LWilson
I don't have a problem doing that at all. I certainly understand your
concern. Thank you!
concern. Thank you!
L
LWilson
Do you know of one that I could use? Because this is a company computer, I'm
not able to go to all websites. It's blocked due to security reasons. If
you don't know of one I can use, then I'll try and post here, but it's
difficult to add everything I need here to give you a good idea of what I'm
doing.
Thanks again!
not able to go to all websites. It's blocked due to security reasons. If
you don't know of one I can use, then I'll try and post here, but it's
difficult to add everything I need here to give you a good idea of what I'm
doing.
Thanks again!
L
LWilson
I found one that would work. Here is the link to the file:
http://fastfreefilehosting.com/getfile.php?
Thanks again!
http://fastfreefilehosting.com/getfile.php?
Thanks again!
L
LWilson
Oops...I used the wrong link. Here is a correct link.
http://fastfreefilehosting.com/file/30713/Days-xls.html
http://fastfreefilehosting.com/file/30713/Days-xls.html
T
T. Valko
Ok, I'm looking at your file.
First, I have to tell you that in my line of work
(non-accounting/non-finance) we don't have fiscal years, we have calendar
years!
In your explanation you say:
How do you arrive at the figure of 88? What's the quarter start date and
what's the quarter end date?
Your table in A21:C32 looks like calander quarters to me.
First, I have to tell you that in my line of work
(non-accounting/non-finance) we don't have fiscal years, we have calendar
years!
In your explanation you say:
How do you arrive at the figure of 88? What's the quarter start date and
what's the quarter end date?
Your table in A21:C32 looks like calander quarters to me.
L
LWilson
Our fiscal year begins July 1, therefore the 1st quarter ends on Sep 30. For
the current quarter, it begins Oct 1 and ends Dec 31, because the calculation
I'm doing for other information in this report, I need the number of days
that have occurred thus far in the current quarter. The table you see, is
just a listing of the months and where they fall in our fiscal year. Column C
indicates the quarter that month falls in my fiscal year.
Hope this helps! And thank you for taking a look!
the current quarter, it begins Oct 1 and ends Dec 31, because the calculation
I'm doing for other information in this report, I need the number of days
that have occurred thus far in the current quarter. The table you see, is
just a listing of the months and where they fall in our fiscal year. Column C
indicates the quarter that month falls in my fiscal year.
Hope this helps! And thank you for taking a look!
T
T. Valko
OK, let's start with this and then go from there...
For the total number of days in a quarter based on any date:
A1 = some date
=SUM(DATE(YEAR(A1),FLOOR(MONTH(A1)-1,3)+{1,4},1)*{-1,1})
If A1 = today's date 12/29/2009 the formula returns 92.
For the total number of days in a quarter based on any date:
A1 = some date
=SUM(DATE(YEAR(A1),FLOOR(MONTH(A1)-1,3)+{1,4},1)*{-1,1})
If A1 = today's date 12/29/2009 the formula returns 92.
L
LWilson
Ok...that works perfectly.
T. Valko said:
T
T. Valko
Good deal.
Is that it? Anything else we need to do with this?
Is that it? Anything else we need to do with this?
L
LWilson
So now what about the other quarters and months? This would work for the
current quarter. Would I need some kind of if statement?
current quarter. Would I need some kind of if statement?
L
LWilson
The formula you gave returns "92" no matter what the date it is. That's not
going to work if the number of days in the quarter is 90 or 91. I have the
following formula that works,for my purposes, except when the current date
falls in the current quarter.
=IF(MID(E$1,8,1)="Q",VLOOKUP(E$3,$A$34:$B$38,2,FALSE),IF(MID(E$1,8,5)="TOTAL",(E$4-$D$4),DAY(E$4)))
This formula can be put into the file I posted on the file hosting site and
copied across. You'll see the NA for the column with FY2010.Q2 in H2.
The vlookup table looks like this:
2009.SEP.30 92
2009.DEC.31 92
2010.MAR.31 90
2010.JUN.30 91
2010.JUN.30 365
The problem arises with the current date. Since the vlookup doesn't have
every date possible, then I get an NA in the result for the current quarter.
Look at the file I posted again. As the current date changes, the current
quarter will need to show the correct number of days that have occurred thus
far in the current quarter. After the current quarter is over, the vlookup
works perfectly.
This is a complicated one!!
going to work if the number of days in the quarter is 90 or 91. I have the
following formula that works,for my purposes, except when the current date
falls in the current quarter.
=IF(MID(E$1,8,1)="Q",VLOOKUP(E$3,$A$34:$B$38,2,FALSE),IF(MID(E$1,8,5)="TOTAL",(E$4-$D$4),DAY(E$4)))
This formula can be put into the file I posted on the file hosting site and
copied across. You'll see the NA for the column with FY2010.Q2 in H2.
The vlookup table looks like this:
2009.SEP.30 92
2009.DEC.31 92
2010.MAR.31 90
2010.JUN.30 91
2010.JUN.30 365
The problem arises with the current date. Since the vlookup doesn't have
every date possible, then I get an NA in the result for the current quarter.
Look at the file I posted again. As the current date changes, the current
quarter will need to show the correct number of days that have occurred thus
far in the current quarter. After the current quarter is over, the vlookup
works perfectly.
This is a complicated one!!
T
T. Valko
Refresh my memory about what you want to do with regards to the other
quarters and months.
quarters and months.
T
T. Valko
The formula you gave returns "92"
Hmmm...
Works OK for me.
Here's a small sample file that demonstrates this.
Days in Quarter.xls 17kb
http://cjoint.com/?mEubT1IWP8
Enter any valid Excel date in cell A1.
Hmmm...
Works OK for me.
Here's a small sample file that demonstrates this.
Days in Quarter.xls 17kb
http://cjoint.com/?mEubT1IWP8
Enter any valid Excel date in cell A1.
L
LWilson
This works great with one problem. If the date is today for instance...the
number of days in the quarter, to this date, is not the full days in the
quarter as of yet. It's only 91 not 92. It will be 92 once it is December
31st. For example, if the current date were 11/15/09, then the number of
days that have passed in the current quarter is 46. If today's date were, in
fact, 12/31/2009 then yes 92 would be the correct number of days.
Hope I've explained that well enough for you to understand what I'm looking
for.
number of days in the quarter, to this date, is not the full days in the
quarter as of yet. It's only 91 not 92. It will be 92 once it is December
31st. For example, if the current date were 11/15/09, then the number of
days that have passed in the current quarter is 46. If today's date were, in
fact, 12/31/2009 then yes 92 would be the correct number of days.
Hope I've explained that well enough for you to understand what I'm looking
for.
T
T. Valko
To count the number of days for the current qtr to today's date:
=TODAY()-DATE(YEAR(NOW()),INT((MONTH(NOW())+2)/3)*3-2,1)+1
Format as General or Number
=TODAY()-DATE(YEAR(NOW()),INT((MONTH(NOW())+2)/3)*3-2,1)+1
Format as General or Number
L
LWilson
Thank you very much for your patience and willingness to help me. I believe
this is going to work when I include in an if statement. Thank you again!!
Lara
this is going to work when I include in an if statement. Thank you again!!
Lara