Hello!

I need to write a formula that allows me to look up two values in a series
of vertical lists and then returns a total value (sumif) from the same table
array that statisfies both criteria.

Something like a vlookup and an AND and a SUMIF althogether if there is such
a beast?

Thanks

Try sumproduct.

=SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100))
where column C is the value you want returned.
--
John C


"Edinburgh" wrote:

Hello!

I need to write a formula that allows me to look up two values in a series
of vertical lists and then returns a total value (sumif) from the same table
array that statisfies both criteria.

Something like a vlookup and an AND and a SUMIF althogether if there is such
a beast?

Thanks

Thank you John C. Unfortunately it didnt work? I'm not sure why not? Do
you have any other ideas??

"John C" wrote:

Try sumproduct.

=SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100))
where column C is the value you want returned.
--
John C


"Edinburgh" wrote:

Hello!

I need to write a formula that allows me to look up two values in a series
of vertical lists and then returns a total value (sumif) from the same table
array that statisfies both criteria.

Something like a vlookup and an AND and a SUMIF althogether if there is such
a beast?

Thanks

With all due respect, "it didnt work" isn't a desperately useful description
of your problem, and (short of clairvoyance) it doesn't give contributors to
the group a great deal of hope of helping you to identify what you've done
wrong.

Exactly what formula did you use? What data values were in the cells
leading into that formula? What result did you get? What result did you
expect?
--
David Biddulph

"Edinburgh" wrote in message
...
Thank you John C. Unfortunately it didnt work? I'm not sure why not? Do
you have any other ideas??

"John C" wrote:

Try sumproduct.

=SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100))
where column C is the value you want returned.
--
John C


"Edinburgh" wrote:

Hello!

I need to write a formula that allows me to look up two values in a
series
of vertical lists and then returns a total value (sumif) from the same
table
array that statisfies both criteria.

Something like a vlookup and an AND and a SUMIF althogether if there is
such
a beast?

Thanks

Hi David,

I realise it is unsatisfactory and I apologise. I'm not a technically
minded person so this is all very mind-boggling to me!

I have a sheet of data which details all the hours recorded by all employees
who each have a 'grade' attached to them and their time is also recorded
against the projects they are working on in that particular period. What I
wanted was a formua that allowed me to return the total of hours worked by
project but also by grade.

I tried the exact formula John suggested
=SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100))
where column C is the value you want returned, but it only returned #N/A.

I seem to have found a suitable solution that should allow me to get the
results that I want, but thank you for your time anyway.

Edinburgh

"David Biddulph" wrote:

With all due respect, "it didnt work" isn't a desperately useful description
of your problem, and (short of clairvoyance) it doesn't give contributors to
the group a great deal of hope of helping you to identify what you've done
wrong.

Exactly what formula did you use? What data values were in the cells
leading into that formula? What result did you get? What result did you
expect?
--
David Biddulph

"Edinburgh" wrote in message
...
Thank you John C. Unfortunately it didnt work? I'm not sure why not? Do
you have any other ideas??

"John C" wrote:

Try sumproduct.

=SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100))
where column C is the value you want returned.
--
John C


"Edinburgh" wrote:

Hello!

I need to write a formula that allows me to look up two values in a
series
of vertical lists and then returns a total value (sumif) from the same
table
array that statisfies both criteria.

Something like a vlookup and an AND and a SUMIF althogether if there is
such
a beast?

Thanks

With that formula, you would need to have defined names for value1 and
value2. What John presumably intended you to do was to replace value1 and
value2 in the formula either by numbers which you were looking for, or by
text strings, or by a reference to the cells in which the values you are
looking for would be found.

So perhaps
=SUMPRODUCT(--($A$1:$A$100=42),--($B$1:$B$100=99),($C$1:$C$100)) or
=SUMPRODUCT(--($A$1:$A$100="value1"),--($B$1:$B$100="value2"),($C$1:$C$100))
or
=SUMPRODUCT(--($A$1:$A$100=D$1),--($B$1:$B$100=E$1),($C$1:$C$100))
--
David Biddulph

"Edinburgh" wrote in message
...
Hi David,

I realise it is unsatisfactory and I apologise. I'm not a technically
minded person so this is all very mind-boggling to me!

I have a sheet of data which details all the hours recorded by all
employees
who each have a 'grade' attached to them and their time is also recorded
against the projects they are working on in that particular period. What
I
wanted was a formua that allowed me to return the total of hours worked by
project but also by grade.

I tried the exact formula John suggested
=SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100))
where column C is the value you want returned, but it only returned
#N/A.

I seem to have found a suitable solution that should allow me to get the
results that I want, but thank you for your time anyway.

Edinburgh

"David Biddulph" wrote:

With all due respect, "it didnt work" isn't a desperately useful
description
of your problem, and (short of clairvoyance) it doesn't give contributors
to
the group a great deal of hope of helping you to identify what you've
done
wrong.

Exactly what formula did you use? What data values were in the cells
leading into that formula? What result did you get? What result did you
expect?
--
David Biddulph

"Edinburgh" wrote in message
...
Thank you John C. Unfortunately it didnt work? I'm not sure why not?
Do
you have any other ideas??

"John C" wrote:

Try sumproduct.

=SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100))
where column C is the value you want returned.
--
John C


"Edinburgh" wrote:

Hello!

I need to write a formula that allows me to look up two values in a
series
of vertical lists and then returns a total value (sumif) from the
same
table
array that statisfies both criteria.

Something like a vlookup and an AND and a SUMIF althogether if there
is
such
a beast?

Thanks