Sumif / Countif Visible Cells Only

Evagrius Ponticus said:

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!

Code:

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

Code:

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

Click to expand...

=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))

Aladin Akyurek said:

Try...

Code:

=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))

Click to expand...

GK039 said:

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

Click to expand...

Aladin Akyurek said:

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?

Click to expand...

GK039 said:

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?

Click to expand...

Aladin Akyurek said:

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.

Click to expand...