Sum Lookup

teknovision · Jun 16th 2004

Hi all!!

I have two columns, Column AF contains a Yes or No drop down (Range AF25 to AF39) and AC contains in times in the hh:mm format (AC25 to AC39). I then have a cell G33 which contains the total amount of time.

I would like G33 to SUM only the times that have 'No', example below:

Yes 1:00
No 0:10
No 0:05

In the above example, Cell G33 would then display 0:15.

I think it's some sort of LOOKUP formula but just can't get my head around this one.. . Any help would be muchly appreciated?

Many many thanks,

Phil

uandras · Jun 16th 2004

Hi Phil

try this formula into G33
=SUMIF(AF25:AF39,"No",AC25:AC39)

hope it helps
regards
Uandras

Anonymous · Jun 16th 2004

Try SUMPRODUCT:

=SUMPRODUCT((AF25:AF39="No")*(AC25:AC39))

teknovision · Jun 16th 2004

More than one way to cook an egg.. .

Thanks guys both suggestions work, thank you!!! Why does it always seem so 'easy' once you have the answer!!

shades · Jun 16th 2004

Just a note:

With one criteria, either formula works, but SUMIF tends to be more flexible (full columns reference, if necessary). However, with more than one criteria, then you would use SUMPRODCT. Also, keep in mind that with SUMPRODUCT, you cannot refer to the entire column (i.e. B:B), but rather a definite range (i.e. B2:B100).

Sum Lookup

Participate now!

Share