Answer for:

I want to ask about a formula. Is any way to do?

Message 6 of 14

View entire thread
1 Votes

There are a couple of ways of handling this. One would be to use filtering (highlight your heading row, click the Data tab in the ribbon and click the Filter icon). You can then select the dropdown box on the heading of the column containing your c's and p's and untick the p's. Excel will now only display rows where the first column is a 'c'. You would typically use this in combination with the SUBTOTAL formula for your other columns. For example, if column B contained the total sale amount, using SUBTOTAL at the end of that column will show you the total for only the rows that are displayed (that is, only the rows that start with a 'c').

If you just want to sum a column or two and you do not want to hide rows as you would if you used a filter, then Jojo's suggestion of using SUMIF (or similar formula's like AVERAGEIF) is spot on. Lets say you have two columns; column A contains either 'c' or 'p', and column B contains the sale total. And you have rows 2 through 10 with values. You'd use SUMIF by entering the following formula in cell B11:

That formula says "look at column A for each row 2 through 10. If the value of column A equals c then include the value of column B in the SUM".