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

Hi,
for your 1st question use

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

to your 2nd

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







"J.Scargill" wrote:

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
Hi

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??



"Eduardo" wrote:

Hi,
for your 1st question use

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

to your 2nd

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







"J.Scargill" wrote:

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
Hi

Try

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

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

--
Jacob


"J.Scargill" wrote:

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

That works perfectly Jacob, thankyou ever so much.

"Jacob Skaria" wrote:

Try

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

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

--
Jacob


"J.Scargill" wrote:

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