adding multiple values from matched search results

[sporenta]

Hello. This is my first post, so forgive me if I'm in any way unclea
(or overly detailed for that matter).

I'm trying to write a formula that will add together all the values tha
match a search criteria.

I have a spread sheet that keeps track of rechargeable grocery cards an
how much they are recharged for. The cards are used for fundraising
Every month I am sent a spread sheet from the grocery company telling m
which cards have been used and for how much money. Only some of th
cards are used, so my master list of card numbers does not match up wit
the cards-used list I get on the monthly statements. I need to sort th
data from the monthly statement to match up with the master list of car
numbers.

I have formulas written that do all this, but there is a complication
If the cards were used more than once for that month, they have mor
than one line of data. So, instead of saying card number ##### was use
for $100, there might be a line saying card ##### was used for $25, an
a line saying it was used for $75. My current formula only displays th
value from the first line of data, not the sum of all the lines for
given card number.

I need a formula that will match the card numbers up, look for all the
amounts associated with that number, and display the sum.

Here's how I have the sheet built so far (all data begins in row 3):

Column A: card number
Column B-F: unimportant data (date purchased, etc.)
Columns G-R: the $ amount each card has been used in that monthly cycl
(blank until pasted in from column W)
Column S: totals of all the monthly statements for a given card. Simpl
sum(G:R)
Column T: unsorted card numbers used during a given month (cut an
pasted directly from monthly statements)
Column U: $ amount used on the cards from column T (cut and pasted fro
statement)
Column V: card numbers from column T sorted to match up with car
numbers from column A
Column W: dollar amounts from column U sorted to match up with car
numbers from column V (and A)

Column V and W are the tough ones. Here are my formulas:

Column V: =IF(ISNUMBER(MATCH(A3,T:T,0)),A3,"")

Column W
=IF(ISNUMBER(MATCH(A3,V:V,0)),OFFSET(INDIRECT("t"&MATCH(A3,T:T,0)),0,1,1,1),"")

Whew! That's a lot of info. Again, sorry if it's too much.

To review:
I need to rewrite my formula for column W to display all the values fro
column U that match up with the cards from column T, not just the firs
one.

Sorry for the long post. Any help is MUCH appreciated

 

[sporenta]

Hi Sporenta,

Two quick questions:

1) Which version of Excel are you using? This will determine whic
formulas are to be used for this.

2) Any chance you could post an example with dummy data (i.e. replac
card numbers with 111 and 222 etc. and spoof names if applicable. Thi
will make it far easier to decipher the issue and far quicker to provid
you with a solution.

Thanks.

Spencer.

Sure.

1) I'm using Excel 2007, Mac Version

2)

Column A Column B Column C Column D
Column E Column F Column G
Row 1 Card Number Student Jan. Deposit Statement Card #
$ Amount Sorted # Sorted $ Amount
Row 2 111 Tom 444
10
Row 3 222 Dick 555
10 222 10
Row 4 333 Harry 555
20
Row 5 444 George 555
10 444 10
Row 6 555 Henry 222
10 555 10

In this simplified example, my goal is to have the formula in column
sort out the numbers in column D (I already have a formula to do this)
and have the formula in column G sort the values from column E, addin
together the values for each instance of the card.

I made this example to show that the way I have the formula written now
Column G works fine EXCEPT in the instance of card 555. Because card 55
was used 3 times, once for $20 and twice for $10, cell H6 should rea
$40, but the way my formula's written now, it only reads the firs
instance of card 555, $20. Once I get the formula right, I plan to us
column D-G as work space. I will paste raw data from monthly statement
into columns D and E, columns F and G will sort and calculate, and
would then cut and paste the values from column G to the months deposi
columns (in this example, column C).

For this example, the formulas would read:

Column F: =IF(ISNUMBER(MATCH(A2,D,0)),A2,"")
Column G
=IF(ISNUMBER(MATCH(A2,F:F,0)),OFFSET(INDIRECT("D"&MATCH(A2,D,0)),0,1,1,1),"")

I hope this is what you were looking for with a dummy example. If you'
like a truer reflection of what my sheet looks like, let me know

 

[isabelle]

hi sporenta,

a pivot table would be a good solution to your problem

http://www.exceltip.com/excel_tips/Excel_Pivot_Tables/32.html


--
isabelle



Le 2012-06-07 14:12, sporenta a écrit :

 

[sporenta]

hi sporenta,

a pivot table would be a good solution to your problem

http://www.exceltip.com/excel_tips/Excel_Pivot_Tables/32.html


--
isabelle


Thanks, Isabelle. I'm looking into pivot tables, and they have potentia
to help.

I did figure out the formula I was looking for, however. It reference
the card numbers and adds up all instances:

=SUMIF(A:A,(IF(ISNUMBER(MATCH(C3,A:A,0)),A3,"")),B:B)

Thanks for the reply.

Cheers,
Stev