Count values

[edved81]

Hello all,

I want to count values in a table:

1 - count total of correction in column 1;

2 - count total of correction in column 2;

3 - count the number values in column 2 , when values in column 2 are equal to "SN" or "A DEFINIR" and values in column 4 are diferent than " PLC";

4 - count the number of correction in column 2 , when values in column 2 are equal to "SN" or "A DEFINIR" and values in column 4 are diferent than " PLC", and values in column 2 are different than "SN" or "A DEFINIR".


Kind regards,

Marco

 

[Luke M]

Hi Marco, and welcome to the forum!

Question 4 makes no sense.

when values in column 2 are equal to "SN" or "A DEFINIR" and values in column 4 are diferent than " PLC", and values in column 2 are different than "SN" or "A DEFINIR".

(Bold added by me)
Which one is it?
Also, in the future, I'd suggest not having two columns labeled "2". Made it confusing to read through.

 

[Tiago MF]

Boas Marco

Please validate the attached file.

KR,

 

[edved81]

The original file is this, but i put the columns name with numbers to make it easier to explain

See the attached file now..

Kind regards,

Marco

 

[edved81]

Boas Marco

Please validate the attached file.

KR,

Hi Tiago, and thanks!

It's almost there...

 

[Tiago MF]

Marco please note that:

For questions 1&2, since the formulas I'm suing are array formulas, you need to confirm them by pressing Ctrl+Shift+Enter. If you just press enter it won't show the right result. When you confirm a formula as an array one it should have these parenthesis "{ }" delimiting it, as so for B2:
{=SUM(NOT(ISBLANK(E9:E40))/1)}
(note that you can't simply copy this and press enter, you really need to confirm it with Ctrl+Shift+Enter)
The results I'm getting for those questions are 2 and 7 respectively, which match your filtered tables.
You can further explore how array formulas work right here in chandoo.

Alternatively, if you want to avoid using array formulas, use sumproduct instead:
For B2: =SUMPRODUCT(NOT(ISBLANK(E9:E40))*1)
For B3: =SUMPRODUCT(NOT(ISBLANK(F9:F40))*1)

For question 3, it was my bad, I didn't notice you meant for all values on column 4 and not just the "corrections". It's an easy fix though, just replace it with:
=SUMPRODUCT(((LEFT(B9:B40,2)="SN")+(LEFT(B9:B40,9)="A DEFINIR"))*(D9:D40<>"PLC"))

Best

 

[edved81]

It's working Tiago.

Thanks!