2

I'm trying to count if the following range is "Y" or "S" and contains numbers. I'm trying to exclude cells beginning with "_PVxxxxx_".

I cant use 

COUNTIFS($A:$A,">0",$B:$B,"Y") + COUNTIFS($A:$A,">0",$B:$B,"S")

because the formula considers "_PVxxxxx_" to be more than 0 and includes them in the calculation.

Can anybody help? Thanks alot!

Data Example

clemens
  • 16,716
  • 11
  • 50
  • 65
kchungg
  • 35
  • 1
  • 2
  • 5

1 Answers1

3

The function SUMPRODUCT is quite versatile, typically more flexible for testing than COUNTIFS and SUMIFS.

It should do the trick (see e.g. https://stackoverflow.com/a/27935006/2707864 or https://stackoverflow.com/a/30854706/2707864) with

=SUMPRODUCT(($A:$A>0)*($B:$B="Y")*(ISNUMBER($A:$A))+...

This works, but I am not sure that you need the part ($A:$A>0)* according to the sample you posted (it doesn't hurt anyway).

PS: If you insist on using COUNTIFS you could use a helper column that uses ISNUMBER and gives, e.g., a suitable numeric result (>0 for numeric data, <0 otherwise). Then you would refer to that column within COUNTIFS.

sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185
  • i have numbers such as 12345678 and PV1234. The problem is that the condition ">0" includes PV1234 into the calculation as well as it considers it to be > 0. I'm trying to calculate only pure numbers such as 12345678 – kchungg Nov 01 '17 at 08:58
  • this works, though, because ISNUMBER only works on numbers, probably best not to use whole columns with SUMPRODUCT though.... – barry houdini Nov 01 '17 at 12:13
  • @kchungg - That is precisely the reason for using SUMPRODUCT, you can also test what you need. Plaease try the solution proposed and post feedback. – sancho.s ReinstateMonicaCellio Nov 01 '17 at 12:43
  • @barryhoudini - I most often restrict the ranges used in SUMPRODUCT. In this case, it is up to the OP knowing what he needs. At any rate, for the cases I dealt with, I never saw any impact on performance from using whole columns/rows. – sancho.s ReinstateMonicaCellio Nov 01 '17 at 12:45
  • 1
    @sancho.s Rather than having a `+` outside `SUMPRODUCT` to handle the "Y or S" condition, you can build this into one `SUMPRODUCT`. Replace `($B:$B="Y")` with `(($B:$B="Y")+($B:$B="S"))`. Otherwise I agree with your answer and gave you an up vote. – ImaginaryHuman072889 Nov 01 '17 at 13:24
  • @asancho.s Sorry my comment was more directed at the OP, but especially if switching, as here, from `COUNTIFS` to `SUMPRODUCT` - the former will only calculate using the "used range" when a full column is used - the latter will actually use the whole column – barry houdini Nov 01 '17 at 13:47
  • @sancho.s its working now. thank you so much! i went with the SUMPRODUCT solution as its more straight forward. – kchungg Nov 02 '17 at 10:23