Sumproduct based on adjacent cell ID Code

**avidcat** · 05-02-2009, 05:06 PM

Attached is an example.

Basically I am trying to get the sumproduct of a column of amounts based on the adjacent cells ID code.

For example column B will have codes 1FHD, 2FHS, 3FHG etc. these codes will be mixed otherwise I would have just did a sum of the cells.

Column C would have dollar amounts.

Sumproduct based on adjacent cell ID Code

**martindwilson** · 05-02-2009, 05:12 PM

just use sumif
=SUMIF($B$7:$B$23,B2,$C$7:$C$23) (note remove the colon on b2)
will give total for c7:c23 =1FHD

OR if you really want to keep :
=SUMIF($B$7:$B$23,SUBSTITUTE(B2,":",""),$C$7:$C$23)

**zbor** · 05-02-2009, 05:13 PM

Try this in C2:

=SUMPRODUCT(1*(B7:B23=B2);C7:C23)

Where b2 is 1FHD without :

**avidcat** · 05-02-2009, 05:36 PM

I am not sure what you meant by remove the colon on B2, but this one worked:

=SUMIF($B$7:$B$23,SUBSTITUTE(B2,":",""),$C$7:$C$23)

Thanks.

**avidcat** · 05-03-2009, 01:58 PM

Follow up,

How can I get this to work for two different codes in two different cells? Basically I want to return the sum if either code is associtated with it.

=SUMIF($B$13:$B$500,SUBSTITUTE(D5,":",""),$C$13:$C$500)

so it would be D5 & D6 for the codes.

Thanks.

**zbor** · 05-03-2009, 02:31 PM

=sumif($b$13:$b$500,substitute(d5,":",""),$c$13:$c$500)+sumif($b$13:$b$500,substitute(d6,":",""),$c$13:$c$500)

**avidcat** · 05-03-2009, 02:38 PM

Thanks,

I pretty much did that but I didn't add the sumif again.

Thanks!

Sumproduct based on adjacent cell ID Code

LinkBack

Thread Tools

Rate This Thread

Display

Sumproduct based on adjacent cell ID Code

Re: Sumproduct based on adjacent cell ID Code

Re: Sumproduct based on adjacent cell ID Code

Re: Sumproduct based on adjacent cell ID Code

Re: Sumproduct based on adjacent cell ID Code

Re: Sumproduct based on adjacent cell ID Code

Re: Sumproduct based on adjacent cell ID Code

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions