Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I don't think this should be too complicated for Excel, but I am trying to figure out how to:

1) Determine if a cell in column A contains the text "FF"
2) If so, jump over to the corresponding cell in Column D and add its value
3) Do this all the way down the A column, keeping a running total of all cells in column D if the cell in column A meets the criteria

Basically, I am breaking down a checkbook register by transaction types, such as Groceries, Clothing, Fast Food, Bills, etc. I want to calculate how much money has gone toward each category.

Any help you can provide will be greatly appreciated. Thank you!

share|improve this question
    
You can do this almost completely with built-ins, the FIND function, as well as the COLUMN and ROW functions. – Breakthrough Mar 18 '13 at 19:03
    
Thank you for the responses. I did figure out how to do this with the SUMIFS function in Excel. I was merely misunderstanding it earlier. Thank God for the internet! =SUMIFS(D2:D9999, A2:A9999, I10) Breakdown: It adds the cell in column D if the cell in the same row in column A contains the criteria in cell I10. I10 contains the text "FF"; If cell A2 contains "FF", then it adds the value from cell D2. It continues this down the line until A9999. Thanks again! – Michael Mar 18 '13 at 19:36

I know, Excel have special function for this. I dont use MS Excel for now, but look next http://tipstricksandwe.blogspot.ru/2011/07/conditional-sum-in-ms-excel.html

=SUMIF(range, criteria, [sum_range])
share|improve this answer

You can do a cumulative conditional sum like this, starting in row 1 and copied down.

=SUMIF($A$1:A1,"*ff*",D:D)

Sumif() is not case-sensitive. The above will find the text "ff" anywhere in the cell. If you want a whole cell match, use

=SUMIF($A$1:A1,"ff",D:D)

Other ways to sum up data can be done with Pivot Tables, without any formulas.

share|improve this answer
    
Thank you. I did change my code to something similar: =SUMIF($A:$A,I10,$D:$D) -- This allowed me to drag it up and down to other cells beside my legend. The only thing that changes is the I10, which is handy for this. – Michael Mar 18 '13 at 19:43
1  
That formula will not give you a running total, though, as you specified in your question. If you want to total by category, a Pivot Table would be much more efficient. – teylyn Mar 18 '13 at 19:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .