hi sporenta,

a pivot table would be a good solution to your problem

http://www.exceltip.com/excel_tips/E...Tables/32.html


--
isabelle



Le 2012-06-07 14:12, sporenta a écrit :
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.