Hi,
I would like to know how I can count only those number of cells in a row
with a particular value which have their adjacent cell on the left without
any value
Example
A B C D E F
1 w 0 w W
2
3
The answer to above should return value of 2

Try sumproduct:
=SUMPRODUCT(--(A2:E10=""),--(B2:F10<""))

assuming you have date from cols A-F and rows 2-10
--
John C


"Sandesh" wrote:

Hi,
I would like to know how I can count only those number of cells in a row
with a particular value which have their adjacent cell on the left without
any value
Example
A B C D E F
1 w 0 w W
2
3
The answer to above should return value of 2

To add to John's solution, I think what you need is
=SUMPRODUCT(--(A2:E10=""),--(B2:F10="w"))

"John C" wrote:

Try sumproduct:
=SUMPRODUCT(--(A2:E10=""),--(B2:F10<""))

assuming you have date from cols A-F and rows 2-10
--
John C


"Sandesh" wrote:

Hi,
I would like to know how I can count only those number of cells in a row
with a particular value which have their adjacent cell on the left without
any value
Example
A B C D E F
1 w 0 w W
2
3
The answer to above should return value of 2

Hi, John & Sheelo,
Thanks for your support, but it seems I have not been able to explain my
problem.
I have data from A1 to F1 as shown in the example. Rest of the rows(A2:F2
and A3: F3) are all blank and do not contain any value and are irrelevant to
the example I have mentioned. They have just been shown there for reference.

I would like to count only the number of cells in row A1: F1 with the
criteria I have mentioned in my earlier example.

"Sheeloo" wrote:

To add to John's solution, I think what you need is
=SUMPRODUCT(--(A2:E10=""),--(B2:F10="w"))

"John C" wrote:

Try sumproduct:
=SUMPRODUCT(--(A2:E10=""),--(B2:F10<""))

assuming you have date from cols A-F and rows 2-10
--
John C


"Sandesh" wrote:

Hi,
I would like to know how I can count only those number of cells in a row
with a particular value which have their adjacent cell on the left without
any value
Example
A B C D E F
1 w 0 w W
2
3
The answer to above should return value of 2

enter the following into the cell you want the result to be;
=SUMPRODUCT(--(A1:Y1=""),--(B1:Z1="w"))

This assumes your data is upto the column Z in row 1. Simply replace Z by
the last column in your dataset and Y with the second last column in your
data set.

Let us know whether it solves your problem.

"Sandesh" wrote:

Hi, John & Sheelo,
Thanks for your support, but it seems I have not been able to explain my
problem.
I have data from A1 to F1 as shown in the example. Rest of the rows(A2:F2
and A3: F3) are all blank and do not contain any value and are irrelevant to
the example I have mentioned. They have just been shown there for reference.

I would like to count only the number of cells in row A1: F1 with the
criteria I have mentioned in my earlier example.

"Sheeloo" wrote:

To add to John's solution, I think what you need is
=SUMPRODUCT(--(A2:E10=""),--(B2:F10="w"))

"John C" wrote:

Try sumproduct:
=SUMPRODUCT(--(A2:E10=""),--(B2:F10<""))

assuming you have date from cols A-F and rows 2-10
--
John C


"Sandesh" wrote:

Hi,
I would like to know how I can count only those number of cells in a row
with a particular value which have their adjacent cell on the left without
any value
Example
A B C D E F
1 w 0 w W
2
3
The answer to above should return value of 2

Dear Sheeloo,

Thanks for your reply but unfortunately it dose not work.

Regards
Sandesh

"Sheeloo" wrote:

enter the following into the cell you want the result to be;
=SUMPRODUCT(--(A1:Y1=""),--(B1:Z1="w"))

This assumes your data is upto the column Z in row 1. Simply replace Z by
the last column in your dataset and Y with the second last column in your
data set.

Let us know whether it solves your problem.

"Sandesh" wrote:

Hi, John & Sheelo,
Thanks for your support, but it seems I have not been able to explain my
problem.
I have data from A1 to F1 as shown in the example. Rest of the rows(A2:F2
and A3: F3) are all blank and do not contain any value and are irrelevant to
the example I have mentioned. They have just been shown there for reference.

I would like to count only the number of cells in row A1: F1 with the
criteria I have mentioned in my earlier example.

"Sheeloo" wrote:

To add to John's solution, I think what you need is
=SUMPRODUCT(--(A2:E10=""),--(B2:F10="w"))

"John C" wrote:

Try sumproduct:
=SUMPRODUCT(--(A2:E10=""),--(B2:F10<""))

assuming you have date from cols A-F and rows 2-10
--
John C


"Sandesh" wrote:

Hi,
I would like to know how I can count only those number of cells in a row
with a particular value which have their adjacent cell on the left without
any value
Example
A B C D E F
1 w 0 w W
2
3
The answer to above should return value of 2

I forgot to mention that you need to press CTRL-SHIFT-ENTER after typing the
formula as this is an ARRAY formula...

It should work. I tested it out. Send your file (or just a mail so that I
get your id) to me at and I will enter the formula and
send back to you.

"Sandesh" wrote:

Dear Sheeloo,

Thanks for your reply but unfortunately it dose not work.

Regards
Sandesh

"Sheeloo" wrote:

enter the following into the cell you want the result to be;
=SUMPRODUCT(--(A1:Y1=""),--(B1:Z1="w"))

This assumes your data is upto the column Z in row 1. Simply replace Z by
the last column in your dataset and Y with the second last column in your
data set.

Let us know whether it solves your problem.

"Sandesh" wrote:

Hi, John & Sheelo,
Thanks for your support, but it seems I have not been able to explain my
problem.
I have data from A1 to F1 as shown in the example. Rest of the rows(A2:F2
and A3: F3) are all blank and do not contain any value and are irrelevant to
the example I have mentioned. They have just been shown there for reference.

I would like to count only the number of cells in row A1: F1 with the
criteria I have mentioned in my earlier example.

"Sheeloo" wrote:

To add to John's solution, I think what you need is
=SUMPRODUCT(--(A2:E10=""),--(B2:F10="w"))

"John C" wrote:

Try sumproduct:
=SUMPRODUCT(--(A2:E10=""),--(B2:F10<""))

assuming you have date from cols A-F and rows 2-10
--
John C


"Sandesh" wrote:

Hi,
I would like to know how I can count only those number of cells in a row
with a particular value which have their adjacent cell on the left without
any value
Example
A B C D E F
1 w 0 w W
2
3
The answer to above should return value of 2

Sheeloo,

I sent u mail on the address u gave but it came back undelivered. My email
ID is

Regards

"Sheeloo" wrote:

I forgot to mention that you need to press CTRL-SHIFT-ENTER after typing the
formula as this is an ARRAY formula...

It should work. I tested it out. Send your file (or just a mail so that I
get your id) to me at and I will enter the formula and
send back to you.

"Sandesh" wrote:

Dear Sheeloo,

Thanks for your reply but unfortunately it dose not work.

Regards
Sandesh

"Sheeloo" wrote:

enter the following into the cell you want the result to be;
=SUMPRODUCT(--(A1:Y1=""),--(B1:Z1="w"))

This assumes your data is upto the column Z in row 1. Simply replace Z by
the last column in your dataset and Y with the second last column in your
data set.

Let us know whether it solves your problem.

"Sandesh" wrote:

Hi, John & Sheelo,
Thanks for your support, but it seems I have not been able to explain my
problem.
I have data from A1 to F1 as shown in the example. Rest of the rows(A2:F2
and A3: F3) are all blank and do not contain any value and are irrelevant to
the example I have mentioned. They have just been shown there for reference.

I would like to count only the number of cells in row A1: F1 with the
criteria I have mentioned in my earlier example.

"Sheeloo" wrote:

To add to John's solution, I think what you need is
=SUMPRODUCT(--(A2:E10=""),--(B2:F10="w"))

"John C" wrote:

Try sumproduct:
=SUMPRODUCT(--(A2:E10=""),--(B2:F10<""))

assuming you have date from cols A-F and rows 2-10
--
John C


"Sandesh" wrote:

Hi,
I would like to know how I can count only those number of cells in a row
with a particular value which have their adjacent cell on the left without
any value
Example
A B C D E F
1 w 0 w W
2
3
The answer to above should return value of 2