Prev: Autofit height
Next: VLook-Up Possibly Conditional Formatting
From: Seanie on 5 Mar 2010 11:49
How could I achieve the following via formula

I want to add up all the instances that certain numbers appear in Col
B with their corresponding values in COL D e.g. if 1 or 3 or 6 or 20
or 26 or 30 or 55 appears in Row1 then add the value that appears in
Row1 COL D and do this for every row in sheet where a values exist in
COL B.

I know how I could do it if I was looking for only 1 instance in ColB,
but the multi instances has me guessing

Thanks
From: Fred Smith on 5 Mar 2010 12:09
You want Sumif, as in:
=sumif(B:B,1,D:D)

Regards,
Fred

"Seanie" <seanryanie(a)yahoo.co.uk> wrote in message
news:1bc12ed6-cdb2-4f38-9125-4127527d2a5b(a)v20g2000yqv.googlegroups.com...
> How could I achieve the following via formula
>
> I want to add up all the instances that certain numbers appear in Col
> B with their corresponding values in COL D e.g. if 1 or 3 or 6 or 20
> or 26 or 30 or 55 appears in Row1 then add the value that appears in
> Row1 COL D and do this for every row in sheet where a values exist in
> COL B.
>
> I know how I could do it if I was looking for only 1 instance in ColB,
> but the multi instances has me guessing
>
> Thanks

From: Seanie on 5 Mar 2010 14:02
Thanks, that would be ideal if I could do =SUMIF(B:B,1,2,3,4,5,D:D),
but it only takes 1 criteria


From: JLatham on 5 Mar 2010 15:48
If the ,1,2,3,4,5 part means sum them if value in B equals any one of those 5
values, then simply write a longish formula for all cases:

=SUMIF(B:B,1,D:D)+SUMIF(B:B,2,D:D)+SUMIF(B:B,3,D:D)+SUMIF(B:B,4,D:D)+SUMIF(B:B,5,D:D)

You only have to do it once.

"Seanie" wrote:

> Thanks, that would be ideal if I could do =SUMIF(B:B,1,2,3,4,5,D:D),
> but it only takes 1 criteria
>
>
> .
>
From: Seanie on 5 Mar 2010 16:03
Is there any limit to the 'longish' formula? Something tells me 8 is
the limit

 |  Next  |  Last
Pages: 1 2 3 4
Prev: Autofit height
Next: VLook-Up Possibly Conditional Formatting