Originally Posted by sporenta

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

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

I have a spread sheet that keeps track of rechargeable grocery cards and 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 me which cards have been used and for how much money. Only some of the cards are used, so my master list of card numbers does not match up with the cards-used list I get on the monthly statements. I need to sort the data from the monthly statement to match up with the master list of card 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 more than one line of data. So, instead of saying card number ##### was used for $100, there might be a line saying card ##### was used for $25, and a line saying it was used for $75. My current formula only displays the value from the first line of data, not the sum of all the lines for a 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 cycle (blank until pasted in from column W)
Column S: totals of all the monthly statements for a given card. Simply sum(G:R)
Column T: unsorted card numbers used during a given month (cut and pasted directly from monthly statements)
Column U: $ amount used on the cards from column T (cut and pasted from statement)
Column V: card numbers from column T sorted to match up with card numbers from column A
Column W: dollar amounts from column U sorted to match up with card 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 from column U that match up with the cards from column T, not just the first one.

Sorry for the long post. Any help is MUCH appreciated.