Google Spreadsheet Countif with multiple criteria

Question:

( by Ar974 )

Bonjour, Hello

example:

cells  :            ListPos= UH/UV/S1/S2/DC/P/RH/RS/GRE/FG/JK/ML/DE/CV/........  (From S1 to S25......)

another List    ListC= CA / RTT /P / CET....

column A :      date : 1 janv / 2 janv / 3 janv / 4 janv / 5 janv / 6  janv / 7 janv /........

column B:  Position : UH     / DH     /         / S1      / CA      / RTT     /           /......... :  ----> cell can be clean /  can be fill from values from ListC or ListPos

so  B column B has elements from the ListPos and ListC with different dates

I want to make the sum of numbers of these positions:   S1 UH S2

then my formula will be:    =COUNTif(B6:B40;"S1")+COUNTif(B6:B40;"UH")+COUNTif(B6:B40;"S2") this is ok

but if I want X criteria (more than 10 for example):  =COUNTif(B6:B40;"UH")+COUNTif(B6:B40;"UV")+COUNTif(B6:B40;"S1")+COUNTif(B6:B40;"S2")+COUNTif(B6:B40;"DC");;;;; IT IS VERY LONG .....AND TAKE TIME

Is there no a formula like that :   =COUNT(FILTER(B6:B40 ; B:B="S1:S25")) ???? COUNT(FILTER(B6:B40 ; B:B=ListPos)) ??? not working to. ????

Merci a l'avance.

Thanks for your help in advance.

Solution:

Have a look at the following screenshot:

In the above sheet I have the following formula in Cell D1:

=counta( iferror( filter( B6:B40; match(B6:B40;S1:S25;0) ) ) )

and the following formula in Cell E1:

=counta( iferror( filter( B6:B40; match(B6:B40;S1:S25;0) ) ) ) + counta( iferror( filter( B6:B40; match(B6:B40;T1:T25;0) ) ) )

and the following formula in Cell F1:

=counta(arrayformula(iferror(match(B6:B40;S1:S25;0);iferror(match(B6:B40;T1:T25;0)))))

In the above formulas, I have assumed that you have you have the ListC in Column T (that is T1:T25) you can change the range as per your requirement.

And if you have given the range "S1:S25" name as "ListPos" then you can replace it in the above formula

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive