Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Addition to Formula
I have the following formula;
=SUMIF(AJ4:AJ123,"T1",AI3:AI122)+SUMIF(AK4:AK123," T1",AI3:AI122)+SUMIF(AL4:AL123,"T1",AI3:AI122)+SUM IF(AM4:AM123,"T1",AI3:AI122) Basically if a cell in AJ, AK, AL, or AM has T1 in it then it sums up the cells that are in column AI and one row above where the T1 was located. However it still calculates cells that are hidden, how do I have it only add up visible cells? Any help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Addition to Formula
SUMIF and SUM add all cells in the range hidden or visible. However
SUBTOTAL sums only visible cells. This is particularly useful if you have AutoFilter in place on your data. Suppose Column A has product name and Column B has Sales Quantities; Product name repeats in several rows. Leave two blank rows where your data ends in Column B and write SubTotal instead of Sum. For example =SUBTOTAL(9,B1:B10) Build AutoFilter on Column A and filter on your Product name; some cell may be hidden depending on filter selection. You will find that Subtotal will only add visible cells, not all cells in the range. Aqib Rizvi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Addition Formula | Excel Discussion (Misc queries) | |||
Formula for addition | Excel Worksheet Functions | |||
Addition & multiplication formula | Excel Discussion (Misc queries) | |||
Formula Addition Copy | Excel Discussion (Misc queries) | |||
Progressive Addition Formula | Excel Worksheet Functions |