Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

2 Answers

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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.