Sumif / Countif Visible Cells Only

[Evagrius Ponticus]

Hello - I've been struggling to get these to work . .I would appreciate any help! I am trying to get these formulas to work for visible cells only. Thank you!

=COUNTIF(R12:R526,"<0")

=SUMIF(R12:R526,"<0")

 

[silentbuddha]

Hi,

http://www.ozgrid.com/Excel/excel-subtotal-function.htm

This might help

 

[Aladin Akyurek]

Hello - I've been struggling to get these to work . .I would appreciate any help! I am trying to get these formulas to work for visible cells only. Thank you!

=COUNTIF(R12:R526,"<0")

=SUMIF(R12:R526,"<0")

Try...

=SUMPRODUCT(
   SUBTOTAL(2,OFFSET(R12,ROW(R12:R526)-ROW(R12),,1)),
   --(R12:R526 < 0))
 
=SUMPRODUCT(
   SUBTOTAL(9,OFFSET(R12,ROW(R12:R526)-ROW(R12),,1)),
   --(R12:R526 < 0))

 

[Haseeb Avarakkan]

Try,

For COUNTIF,

=SUMPRODUCT(SUBTOTAL(3,OFFSET(R12:R526,ROW(R12:R526)-ROW(R12),0,1)),--(R12:R526 < 0))

For SUMIF,

=SUMPRODUCT(SUBTOTAL(3,OFFSET(R12:R526,ROW(R12:R526)-ROW(R12),0,1)),--(R12:R526 < 0),R12:R526)

 

[Evagrius Ponticus]

WOW - this is incredible Aladin and Hazeeb - just brilliant!!!

This resolves my obstacle, but if either of you would care to explain how these formulas work - I would be grateful!! Thanks!

 

[GK039]

Try...

=SUMPRODUCT(
   SUBTOTAL(2,OFFSET(R12,ROW(R12:R526)-ROW(R12),,1)),
   --(R12:R526 < 0))
 
=SUMPRODUCT(
   SUBTOTAL(9,OFFSET(R12,ROW(R12:R526)-ROW(R12),,1)),
   --(R12:R526 < 0))

Hello Aladin,

Maybe a silly question but I was just wondering as I was going through this post. Your formulas works great but why should anyone (including myself as i use a lot of your hints) use sumproduct in this specific example since if we just "custom Auto-filter" the list and use Subtotal function it will give us the result that Evagrius was looking for?

Thanx

George

 

[Aladin Akyurek]

Hello Aladin,

Maybe a silly question but I was just wondering as I was going through this post. Your formulas works great but why should anyone (including myself as i use a lot of your hints) use sumproduct in this specific example since if we just "custom Auto-filter" the list and use Subtotal function it will give us the result that Evagrius was looking for?

Thanx

George

Let A2:F400 house a sample, with headers in A2:F2.

Let F3:F400 house the occurrences FAD, GAD, KAD, LAD, YAD, etc.

Lets (custom) AutoFilter on the A range, also say on the C range.

How would you count the occurrences of GAD in the F range?

 

[GK039]

Let A2:F400 house a sample, with headers in A2:F2.

Let F3:F400 house the occurrences FAD, GAD, KAD, LAD, YAD, etc.

Lets (custom) AutoFilter on the A range, also say on the C range.

How would you count the occurrences of GAD in the F range?

Hello again Aladin

Late seen your answer late response...

Thanx! I tried it as an example the way you put it. It works great with numbers but when i tried in column F the strings FAD, GAD, LAD etc and I used:

=SUMPRODUCT(SUBTOTAL(2;OFFSET(F3;ROW(F3:F50)-ROW(F3);;1));--(F3:F50="OAD"))

to return the occurrences of OAD i got a zero. Evaluating the formula gave me an array of Os. Where did i go wrong?

 

[Aladin Akyurek]

Hello again Aladin

Late seen your answer late response...

Thanx! I tried it as an example the way you put it. It works great with numbers but when i tried in column F the strings FAD, GAD, LAD etc and I used:

=SUMPRODUCT(SUBTOTAL(2;OFFSET(F3;ROW(F3:F50)-ROW(F3);;1));--(F3:F50="OAD"))

to return the occurrences of OAD i got a zero. Evaluating the formula gave me an array of Os. Where did i go wrong?

You need to switch from func num 2 [i.e., COUNT] to func num 3 [i.e., COUNTA]... Thus:

=SUMPRODUCT(SUBTOTAL(3;OFFSET(F3;ROW(F3:F50)-ROW(F3);;1));--(F3:F50="OAD"))

for COUNTA counts everything, inculuding text values.

 

[GK039]

You need to switch from func num 2 [i.e., COUNT] to func num 3 [i.e., COUNTA]... Thus:

=SUMPRODUCT(SUBTOTAL(3;OFFSET(F3;ROW(F3:F50)-ROW(F3);;1));--(F3:F50="OAD"))

for COUNTA counts everything, inculuding text values.

Thanx!!