how to combine sumproduct and countif without adding the two of them

[Mushette]

Hello,

I'm having problems with my formula. Here it is :

=SUMPRODUCT((('tableau de bord'!$E$3:$E$269="M1")+('tableau de bord'!$E$3:$E$269="M1 F1")+('tableau de bord'!$E$3:$E$269="M1 F2")+('tableau de bord'!$E$3:$E$269="M1 F3")+('tableau de bord'!$E$3:$E$269="Non")+('tableau de bord'!$E$3:$E$269="SO"))*(('tableau de bord'!$AC$3:$AC$269="Autorisé le")+('tableau de bord'!$AC$3:$AC$269="Envoyé le")+('tableau de bord'!$AC$3:$AC$269="Non Autorisé")+('tableau de bord'!$AC$3:$AC$269="Reconduit le"))*(('tableau de bord'!$AK$3:$AK$269="exploité")+('tableau de bord'!$AK$3:$AK$269="secours")))*(COUNTIF('tableau de bord'!AN3:AN269;"*/ 3 /*"))

The formula seems good but the problem is that sumproduct and countif multiply together.
I just want countif to be another filter. Can you help me ?

Thank you very much.

[icestationzbra]

you are missing one bracket in the right place (hence the multiplicative effect), and also, i think, you want to use COUNTIF to ascertain the existence of a string in a cell, so:

=SUMPRODUCT((('tableau de bord'!$E$3:$E$269="M1")+('tableau de bord'!$E$3:$E$269="M1 F1")+('tableau de bord'!$E$3:$E$269="M1 F2")+('tableau de bord'!$E$3:$E$269="M1 F3")+('tableau de bord'!$E$3:$E$269="Non")+('tableau de bord'!$E$3:$E$269="SO"))*(('tableau de bord'!$AC$3:$AC$269="Autorisé le")+('tableau de bord'!$AC$3:$AC$269="Envoyé le")+('tableau de bord'!$AC$3:$AC$269="Non Autorisé")+('tableau de bord'!$AC$3:$AC$269="Reconduit le"))*(('tableau de bord'!$AK$3:$AK$269="exploité")+('tableau de bord'!$AK$3:$AK$269="secours"))*(COUNTIF('tableau de bord'!AN3:AN269;"*/ 3 /*")>0))

there is no way for me to test this, so the onus is on you.

also, if you share your (sample) workbook, i am quite sure your formula can be simplified to a much greater extent.

UPDATE:

try replacing (COUNTIF('tableau de bord'!AN3:AN269;"*/ 3 /*")>0) with (isnumber(search("*/ 3 /*";'tableau de bord'!AN3:AN269)))...

[Ace_XL]

Untested..

Please Login or Register  to view this content.

[Mushette]

Thank you for your replies.

Here is a sample workbook (I'm working on a bigger one for my company which contains something like 40 columns and 270 lines).
On the sample I only put the columns on which I want to do the filter.



icestationzbra, I tried your formula but it doesn't give me the result I'd like to have.
I don't want to ascertain the existence of a string in a cell for the last part of the formula. I just want the last part to be another filter but I don't know if it is possible to add another sumproduct part to the whole thing.

Ace_XL, I tried yours as well but it doesn't work, it seems that Excel doesn't understand the "{" .


Hmm, I know the last column is kind of a mess but I cannot make it better, I only want to take what contains "/ 3 /"

Thanks

[Mushette]

Well, I just discovered that the column C was missing three values in the formula because lines 261,262,263 appeared with a space behind the word...

But that last part of the formula still doesn't work.

[Ace_XL]

Ice's formula works..see attached

[Mushette]

Indeed, I tried it all again on my own !

Thank you very much, you guys are genius

[icestationzbra]

simplified, abridged, formula for the sample you provided in post #4:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

alternatively, Pivot Table can also be used for this purpose, see attached.

[icestationzbra]

i had been attempting to solve this problem using Database functions, and seem to have succeeded with the help of some reference material from Bill Jelen. Database functions are purported to have better performance, compared to SUMPRODUCT and the like, over large ranges of data.