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.

Hi Sporenta,

Two quick questions:

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

2) Any chance you could post an example with dummy data (i.e. replace card numbers with 111 and 222 etc. and spoof names if applicable. This will make it far easier to decipher the issue and far quicker to provide 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 F 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, adding 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 555 was used 3 times, once for $20 and twice for $10, cell H6 should read $40, but the way my formula's written now, it only reads the first instance of card 555, $20. Once I get the formula right, I plan to use column D-G as work space. I will paste raw data from monthly statements into columns D and E, columns F and G will sort and calculate, and I would then cut and paste the values from column G to the months deposit columns (in this example, column C).

For this example, the formulas would read:

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

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

Sorry for the strange formatting. It looks good when I format it in the previewer, but when I posted it came out weird, including icons I didn't put in. Very sorry for the weirdness. I hope this works.

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.

[quote=isabelle;1602511]hi sporenta,

a pivot table would be a good solution to your problem

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


--
isabelle


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

I did figure out the formula I was looking for, however. It references 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,
Steve