Sum up visible categories

[adamsm]

Hi any one,

How could i make the following formula to sum up visible rows only?
=SUMIF(J17:J100,"Fruits",P17:P100)

Any help on this would be kindly appreciated.

Thanks in advance.

 

[JamesW]

Hi,

You could try SubTotal (applying filters on Fruits will calculate the sum of whats visible).

=SUBTOTAL(9,J17:J100)

 

[adamsm]

I'm sorry but your formula does not work for me. In column J I have the category names like fruits vegetables and so on. In column P I have each day's total for the category.

What I want is when I filter date from column G the total for either fruits or vegetables from column P to get copied to the cell where the formula resides.

I hope I've made my question clear.

 

[Aladin Akyurek]

Hi any one,

How could i make the following formula to sum up visible rows only?
=SUMIF(J17:J100,"Fruits",P17:P100)

Any help on this would be kindly appreciated.

Thanks in advance.

Try...

=SUMPRODUCT(
   SUBTOTAL(9,OFFSET(P17,ROW(P17:P100)-ROW(P17),,1)),
   --(J17:J100="Fruits"))​

​

 

[hiker95]

adamsm,

Try:
=SUBTOTAL(109,J17:J100)


The 109 will ignore hidden values.

 

[adamsm]

Thanks for the help and I do really appreciate it.