Summation by Codes

**addie** · 03-22-2009, 03:39 PM

Codes:-------- Responses:
601BF5 -------- 200
601BF6 -------- 400
601BF7 -------- 500
601BZ5 -------- 200
601BZ6 -------- 400
601BZ7 -------- 500

How would I create a formula which captures "responses" from codes that end in ".7"?

**shg** · 03-22-2009, 03:52 PM

None of those end in ".7", addie.

To sum the ones that end in "7", =SUMPRODUCT( (RIGHT($A$1:$A$6, 1)="7") * $B$1:$B$6)

**DonkeyOte** · 03-22-2009, 03:53 PM

Use standard SUMIF with Wildcard

=SUMIF(A:A,"*7",B:B)

**teylyn** · 03-22-2009, 03:54 PM

Hi,

one possible solution: create a new column where you extract the last character of your Codes and then do a SUMIF based on that new column.

Extract the last character of the Codes

=RIGHT(A1,1)

If your Codes are in column A, your Responses in column B and your extracted last character is in column C, then the follwoing formula will sum all lines where codes end in 7

=SUMIF(C1:C199,7,B1:B199)

hope that helps

**addie** · 03-22-2009, 04:08 PM

Thank you all for your quick responses. They were all very helpful!!!

Shg, quick question on your formula...what if I wanted to add a vlookup formula to the sum product formula?

How could I pull the response data from another page?