USING COUNTIFS and LEFT TOGETHER

[Simonc64]

Does anyone have a solution to this?

For example =COUNTIFS(X:X,"=Y",A:A,"=1",LEFT(C:C,1),"s"))

This formula doesnt work, but explains my dilemma.

Any answers gratefully received!

Thanks in advance

Simon

 

[bryonwoods30]

you can break it apart and say

=countif(X:X,"Y")+countif(A:A,1)+countif(left(c:c,1),"s"))

 

[Jonmo1]

Try
=COUNTIFS(X:X,"=Y",A:A,"=1",C:C,"s*")

 

[Simonc64]

Hi Bryon thanks for the reply, I did try that but Excel still says theres an error with that formula.

 

[Simonc64]

Brilliant thanks Jon, works a treat!

Just a different way of thinking!

 

[Rogue1]

What happened here in the formula? its amazing

 

[Rogue1]

Use SUMPRODUCT instead

=SUMPRODUCT(--(D5:D21="A"),--(LEFT(E5:E21,3)="ABC"))

 

[Aladin Akyurek]

Use SUMPRODUCT instead

=SUMPRODUCT(--(D5:D21="A"),--(LEFT(E5:E21,3)="ABC"))

No need if you have COUNTIFS...

=COUNTIFS(D5:D21,"A",E5:E21,"ABC*")

which will be faster. Invoke SUMPRODUCT (or other kind of array-processing) when the references (ranges) require processing leading to array objects.

 

[Rogue1]

What does * do? I don't know this and this is brilliant indeed.

 

[Aladin Akyurek]

What does * do? I don't know this and this is brilliant indeed.

Here it does function as a wildcard, meaning it stands 0 or more tokens. So:

ABC, ABC2, ABCX, ABCTx, etc. will be picked out as admissible entries.