Basic question regarding multiple cell counts

[Curioxity]

Hi all,

I was wondering if someone could help me with something which I'm sure is simple for the vast majority but I can't seem to get it right.

I'd like to return a count if certain values across multiple cells are met.

Basic outline:

column1 column2

False Failed stage 1
Pass
Pass
False Failed stage 2
False Failed stage 2

Ideally I'd like to return a count of anything in column1 that was false + Failed in stage 2 and likewise for stage 1 failures.

I've tried playing with dcount, sum functions etc. but I can't seem to get it working.

Thanks,

Kate

 

[VoG]

Try

=SUMPRODUCT(--(A1:A10=False),--(B1:B10="Failed stage 2"))

 

[anglais428]

try countifs

=COUNTIFS(your_range_1,"False",your_range_2,"Failed in stage 2")

Range 1 should be your column 1 range

 

[JackDanIce]

Will False in column A always have a value of Failed in column B (and then Stage1 and Stage2 listed in column C)? If so, try:

=SUMPRODUCT(--($A$1:$A$5=FALSE),--($C$1:$C$5="Stage1"))
=SUMPRODUCT(--($A$1:$A$5=FALSE),--($C$1:$C$5="Stage2"))

 

[JackDanIce]

I believe COUNTIFS function is only available on Excel versions 2007 and higher...

 

[Curioxity]

Thanks all that worked perfectly

 

[Curioxity]

Hello,

Would anyone be able to follow-up on their previous solution with a mini one? I'd like to change the 2nd array to find partial matches as one of the categories is a CS failure but there's many variants of CS failures and I don't want to have to add each individual one i.e -

Pass
Fail CS:00387
Fail CS:00389

=SUMPRODUCT(--('Stage Process'!B2:B65536="FAIL"),--('Fail category'!C2:C65536="CS(How do I add the partial?))

This thing seems to be growing bigger and bigger as various departments add their own twists!

Thanks,

Kate

 

[T. Valko]

Hello,

Would anyone be able to follow-up on their previous solution with a mini one? I'd like to change the 2nd array to find partial matches as one of the categories is a CS failure but there's many variants of CS failures and I don't want to have to add each individual one i.e -

Pass
Fail CS:00387
Fail CS:00389

=SUMPRODUCT(--('Stage Process'!B2:B65536="FAIL"),--('Fail category'!C2:C65536="CS(How do I add the partial?))

This thing seems to be growing bigger and bigger as various departments add their own twists!

Thanks,

Kate

Maybe something like this...

=SUMPRODUCT(--('Stage Process'!B2:B65536="FAIL"),--(LEFT('Fail category'!C2:C65536,2)="CS"))

 

[Curioxity]

Maybe something like this...

=SUMPRODUCT(--('Stage Process'!B2:B65536="FAIL"),--(LEFT('Fail category'!C2:C65536,2)="CS"))

I'll give that a try thank you