Formula for multiple countifs

[J.Scargill]

Hi,
I would really appreciate anybodys expertise with the following 2 formulas;
I have a worksheet that looks a bit like this - (A is days taken to deliver
- B is method for delivery)

A B
1 sec *I need to be able to count the number of cells in A
that contain
1 sig a '1' but only if they have either 'sec','sig' or
'del' in column B.
1 sec
1 man **Then I need to count the number of cells in A that
are greater 1 del or equal to '4' but DONT contain 'sec',
'sig' or 'del'.
2 sec
2 sig Note there are several more possible values that
turn up in B,
3 sec do I need to let you have all of these too?
3 van
3 sec
3 dup
4 sig
4 man
7 sig

 

[Eduardo]

Hi,
for your 1st question use

=SUMPRODUCT((A1:A2=1)*(B1:B2="Sec")*(B1:B2="Sig")*(B1:B2="Del"))

to your 2nd

=SUMPRODUCT((A1:A2>1)*(A1:A2<=4)*(B1:B2<>"Sec")*(B1:B2<>"Sig")*(B1:B2<>"Del"))

 

[J.Scargill]

Hi Eduardo,

Thanks, but the first formula returns a zero. Any ideas why?? There are
blank cells in the colums, does that matter?? The actual range i am using is
A3:A10000, does this matter??

 

[Jacob Skaria]

Try

'1st
=SUMPRODUCT((A1:A100=1)*(B1:B100={"sec","sig","del"}))

'and 2nd
=SUMPRODUCT((A1:A100>1)*(A1:A100<=4)*
(ISNA(MATCH(B1:B100,{"sec","sig","del"},0))))

 

[J.Scargill]

That works perfectly Jacob, thankyou ever so much.