Addition to Formula

[lightbulb]

I have the following formula;

=SUMIF(AJ4:AJ123,"T1",AI3:AI122)+SUMIF(AK4:AK123,"T1",AI3:AI122)+SUMIF(AL4:AL123,"T1",AI3:AI122)+SUMIF(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?

 

[Aqib Rizvi]

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