Hi folks,

I have a flat table with 3 columns
Name Time Value
-------------------------
John 0:30 7
Peter 0:40 8
Joe 0:50 1
John 1:30 88

Can I use somehow a function in order to get f.ex. the value for John,
i.e.
=F(Value,Peter,0:40)
DGet has similar functionality, but the criteria expression a column
containing a range

It seems I need something like GetPivotData, but I don't have a Pivot table.
Of course I can build one and get my values, but it seems not the right
solution
I have another solution also - to concatenate the first 2 columns and later
on to use SumIf, where the range will be the concatenated column and the
criteria will be concatenation of my 2 fields..

Is there any other more elegant solution?

Thanks,
Yakimoto

You can use SumProduct:

=SUMPRODUCT(($A$2:$A$5=$F2)*($B$2:$B$5=G$1)*($C$2: $C$5))

where F2=John and G1=0:30

Yakimo wrote:
Hi folks,

I have a flat table with 3 columns
Name Time Value
-------------------------
John 0:30 7
Peter 0:40 8
Joe 0:50 1
John 1:30 88

Can I use somehow a function in order to get f.ex. the value for John,
i.e.
=F(Value,Peter,0:40)
DGet has similar functionality, but the criteria expression a column
containing a range

It seems I need something like GetPivotData, but I don't have a Pivot table.
Of course I can build one and get my values, but it seems not the right
solution
I have another solution also - to concatenate the first 2 columns and later
on to use SumIf, where the range will be the concatenated column and the
criteria will be concatenation of my 2 fields..

Is there any other more elegant solution?

Thanks,
Yakimoto




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

thanks, Debra

that is what i needed
yakimoto

"Debra Dalgleish" wrote in message
...
You can use SumProduct:

=SUMPRODUCT(($A$2:$A$5=$F2)*($B$2:$B$5=G$1)*($C$2: $C$5))

where F2=John and G1=0:30

Yakimo wrote:
Hi folks,

I have a flat table with 3 columns
Name Time Value
-------------------------
John 0:30 7
Peter 0:40 8
Joe 0:50 1
John 1:30 88

Can I use somehow a function in order to get f.ex. the value for John,
i.e.
=F(Value,Peter,0:40)
DGet has similar functionality, but the criteria expression a column
containing a range

It seems I need something like GetPivotData, but I don't have a Pivot
table.
Of course I can build one and get my values, but it seems not the right
solution
I have another solution also - to concatenate the first 2 columns and
later
on to use SumIf, where the range will be the concatenated column and the
criteria will be concatenation of my 2 fields..

Is there any other more elegant solution?

Thanks,
Yakimoto




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra, your solution is perfect for me, when I deal with numbers in Value
column.
Do you know how I can do the same when my value is not a number, but a
string?
Example
John 0:30 Food

=func("John","0:30")
I expect to return "Food"

Thanks,
Yakimo

"Debra Dalgleish" wrote in message
...
You can use SumProduct:

=SUMPRODUCT(($A$2:$A$5=$F2)*($B$2:$B$5=G$1)*($C$2: $C$5))

where F2=John and G1=0:30

Yakimo wrote:
Hi folks,

I have a flat table with 3 columns
Name Time Value
-------------------------
John 0:30 7
Peter 0:40 8
Joe 0:50 1
John 1:30 88

Can I use somehow a function in order to get f.ex. the value for John,
i.e.
=F(Value,Peter,0:40)
DGet has similar functionality, but the criteria expression a column
containing a range

It seems I need something like GetPivotData, but I don't have a Pivot
table.
Of course I can build one and get my values, but it seems not the right
solution
I have another solution also - to concatenate the first 2 columns and
later
on to use SumIf, where the range will be the concatenated column and the
criteria will be concatenation of my 2 fields..

Is there any other more elegant solution?

Thanks,
Yakimoto




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html