Mr Excel & excelisfun Trick #5: Horizontal Subtotals

See Mr Excel and excelisfun try and create Horizontal Subtotals for a data set. See the IF, SUM and SUMIF functions as they try to solve this very difficult problem. Also see Conditional Formatting for non-contguous cell ranges using a TRUE FALSE logical formula with the NOT symbols.

Category:

Science & Technology

Tags:

• Excel
• Horizontal
• Subtotals
• SUMIF
• function
• formula
• excelisfun
• Bill
• Mr
• Jelen
• Mike
• Gel
• Girvin
• Highline
• Community
• College
• Duel
• Dueling

[gwacktjnorth]

hI, i am quite late in watching but it is still very educational indded. However, i was just wondering if you can use the vertical subtotal and copy paste special transpose?

[ExcelIsFun]

@gwacktjnorth , Yes, absolutely! That is a great method.

[2107Gopal]

Hi,

Again it was a great lesson to me, but Mr Excel when i was going through your lesson by myself , I got stuck in 2:30secs in your video , when I am trying to select the blanks and was putting in the formulae =left arrow to get the same month as the previous cell i could get the result of the formulae only in that cell where i have put in the formulae, rest of the blank cells are showing that they are already selected but they are still blank. Request you to kindly resolve this.

[ExcelIsFun]

1) select blanks

2) type formula

3) Ctrl + Enter

You probably hit Enter, instead of Ctrl + Enter.

[chinchonchinchon]

Excellsfun, your method is simpler, but hey! Mr.Excel's also contains nuggets of information that can be useful in other situations.

Thanks both!

[ExcelIsFun]

You are welcome!

[ExcelIsFun]

You are welcome!

[fressy98]

That's strange. I tried it at work and it doesn't work but now that I try it at home(not the same file), it works. So bad I don't have the original file to look at it more closely. But that's good to know that it should work. Thanks for your help!

[ExcelIsFun]

When I hit the F5 key and then Special and then Errors and then check only Error, I get all errors selected, including #N?A errors.

[fressy98]

In your video, you showed that when we press on the F15 key and click on special, we can make it select different kind of cases. I have used it to select only blank cases or error cases with success. But it doesn't work when I want it to select #N/A cases.