J
Jan I
I need to look up the *Amount* based on two criteria (Provider and Date). Is
there a built-in way to do that in Excel?
Jan
there a built-in way to do that in Excel?
Jan
T
T. Valko
Where is the amount?
Where is and what are the criteria?
Where is and what are the criteria?
J
Jan :\)
Hi Biff,
We have clients that buy sections of seats, much like a Ticket agent, and
when the seats get used up to so that there is only 25% of the current
inventory left, they will need to purchase more seats.
We have Client (Provider)
Date
Usage
Percentage
We also need to be able to calculate the number of remaining seats using the
Provider and Date as the criteria to find the amount of seats left for a
specific client by date. We have been able to do a manual calculation to
get this information, but, not being all that familiar with Excel on that
level, we wondered if there was a built-in function or calculation that will
do that.
Jan
We have clients that buy sections of seats, much like a Ticket agent, and
when the seats get used up to so that there is only 25% of the current
inventory left, they will need to purchase more seats.
We have Client (Provider)
Date
Usage
Percentage
We also need to be able to calculate the number of remaining seats using the
Provider and Date as the criteria to find the amount of seats left for a
specific client by date. We have been able to do a manual calculation to
get this information, but, not being all that familiar with Excel on that
level, we wondered if there was a built-in function or calculation that will
do that.
Jan
T
T. Valko
Well, you haven't answered my questions so I guess I have to coax the
information out of you !
.............A..........B............C
1......Client.....Date.....Amount
2.........A.......1/1/08........10
3.........B.......2/1/08........15
4.........A.......5/3/08........17
5.........C.......1/1/08........12
If I want to find the amount for Client A for date 5/3/08:
=SUMPRODUCT(--(A2:A5="A"),--(B2:B5=DATE(2008,5,3)),C2:C5)
The result is: 17
It's better to use cells to hold the criteria:
E2 = A
F2 = 5/3/08
=SUMPRODUCT(--(A2:A5=E2),--(B2:B5=F2),C2:C5)
Tell me that the clients are listed in the range A2:A5, the dates are in the
range B2:B5 and the amount to be returned is in the range C2:C5. That's the
kind of information I need to be able to help you!
information out of you !
.............A..........B............C
1......Client.....Date.....Amount
2.........A.......1/1/08........10
3.........B.......2/1/08........15
4.........A.......5/3/08........17
5.........C.......1/1/08........12
If I want to find the amount for Client A for date 5/3/08:
=SUMPRODUCT(--(A2:A5="A"),--(B2:B5=DATE(2008,5,3)),C2:C5)
The result is: 17
It's better to use cells to hold the criteria:
E2 = A
F2 = 5/3/08
=SUMPRODUCT(--(A2:A5=E2),--(B2:B5=F2),C2:C5)
Tell me that the clients are listed in the range A2:A5, the dates are in the
range B2:B5 and the amount to be returned is in the range C2:C5. That's the
kind of information I need to be able to help you!
J
Jan :\)
T. Valko said:
Sorry, didn't understand what specific information you were looking for.
Yes, that is how it is listed.
A B C
Provider Date Order Qty
P-A 5/5/2008 100
P-B 5/6/2008 200
P-C 5/1/2008 20
P-B 6/1/2008 500
Thank you.
Jan
T
T. Valko
Ok, we're making progress.
Based on the sample data you posted what result do you expect?
Based on the sample data you posted what result do you expect?
D
Don Guillett
Biff, Don't you wish that you had taken up dentistry?
J
Jan :\)
T. Valko said:
To be able to get a total amount for a specific Client for a specific date.
I have a pivot table that shows the most recent purchase *Date* for each
Provider, but, had to use a custom function because I couldn't figure out
how to look up the *Amount* based on two criteria (Provider and Date). So
what I was wondering is if there is a built-in function that I could use to
calculate that information instead having to create a custom function.
If there is no built-in function that will provide that type of calcualtion,
then that is fine, but, I was interested to know if one did exist that I
could use instead. If so, what would that funciton be?
Jan
J
Jan :\)
Ahh.....nice shot. If I had understood his question from the start it would
have saved time for me too.
Have a great day.
Jan
have saved time for me too.
Have a great day.
Jan
D
Don Guillett
The point is that it is incumbent on YOU to FULLY explain your question. No
mind readers here.
mind readers here.
T
T. Valko
Don't the sample formulas I posted do what you want? Based on your
explanation they should.
=SUMPRODUCT(--(client_range="client"),--(date_range=DATE(....)),amount_range)
explanation they should.
=SUMPRODUCT(--(client_range="client"),--(date_range=DATE(....)),amount_range)
T
T. Valko
Yeah
If I were a dentist I'd probably be out on the golf course right now!
If I were a dentist I'd probably be out on the golf course right now!
D
Dave Peterson
So you'd be filling holes in either case????
T. Valko said:
T
T. Valko
LOL!
--
Biff
Microsoft Excel MVP
--
Biff
Microsoft Excel MVP
Dave Peterson said:
J
Jan :\)
Yes, they did, thank you. But......you didn't ask if they did, you just
asked what I expected.
Thanks, Biff.
Jan
asked what I expected.
Thanks, Biff.
Jan
J
Jan :\)
But..... I did FULLY explain my actual question. If you *really* read my
original post, all I asked was, "Is there a built-in way to do that in
Excel?"
Jan
original post, all I asked was, "Is there a built-in way to do that in
Excel?"
Jan
T
T. Valko
You're welcome!
--
Biff
Microsoft Excel MVP
--
Biff
Microsoft Excel MVP
Jan :) said:Yes, they did, thank you. But......you didn't ask if they did, you just
asked what I expected.
Thanks, Biff.
Jan