Double count
 

I have the following data:

w 1
w 1
w 2
w 1
w 1
c 3
w 2
c 1
c 1
w 3
c 1

I need a formula that calculates how many of the w's have numbers greater
than 1. I was trying this formula: sumproduct(--(C2:C12="W"),--(D2:D121))
but it doesn't work.

Double count
 

Your formula works for me. Are you sure that the *numbers* are real nunbers
and not text?

Try:

=ISTEXT(D2) and copy down to see what you get.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Byron720" wrote in message
...
I have the following data:

w 1
w 1
w 2
w 1
w 1
c 3
w 2
c 1
c 1
w 3
c 1

I need a formula that calculates how many of the w's have numbers greater
than 1. I was trying this formula: sumproduct(--(C2:C12="W"),--(D2:D121))
but it doesn't work.

Double count
 

Use lowercase w
--
Gary''s Student - gsnu200765


"Byron720" wrote:

I have the following data:

w 1
w 1
w 2
w 1
w 1
c 3
w 2
c 1
c 1
w 3
c 1

I need a formula that calculates how many of the w's have numbers greater
than 1. I was trying this formula: sumproduct(--(C2:C12="W"),--(D2:D121))
but it doesn't work.

Double count
 

Try this

=SUMPRODUCT((C2:C12="w")*(D2:D121))

Mike

"Byron720" wrote:

I have the following data:

w 1
w 1
w 2
w 1
w 1
c 3
w 2
c 1
c 1
w 3
c 1

I need a formula that calculates how many of the w's have numbers greater
than 1. I was trying this formula: sumproduct(--(C2:C12="W"),--(D2:D121))
but it doesn't work.

Double count
 

Use a pivot table with w,c on rows and 1,2 on columns and in data
value
Post that you can select w on row side and unselect 1 on column side
to get the exact count
Regards
Anirudh

On Jan 11, 10:41*pm, Byron720
wrote:
I have the following data:

w * * * 1
w * * * 1
w * * * 2
w * * * 1
w * * * 1
c * * * 3
w * * * 2
c * * * 1
c * * * 1
w * * * 3
c * * * 1

I need a formula that calculates how many of the w's have numbers greater
than 1. I was trying this formula: sumproduct(--(C2:C12="W"),--(D2:D121))
but it doesn't work.

Double count
 

You may have spaces after the letters in C2:C12. "w " is not equal to "w".

Tyro

"Byron720" wrote in message
...
I have the following data:

w 1
w 1
w 2
w 1
w 1
c 3
w 2
c 1
c 1
w 3
c 1

I need a formula that calculates how many of the w's have numbers greater
than 1. I was trying this formula: sumproduct(--(C2:C12="W"),--(D2:D121))
but it doesn't work.

Double count
 

Use lowercase w

To an XL formula W and w are equal. ="W"="w" returns TRUE

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Gary''s Student" wrote in message
...
Use lowercase w
--
Gary''s Student - gsnu200765


"Byron720" wrote:

I have the following data:

w 1
w 1
w 2
w 1
w 1
c 3
w 2
c 1
c 1
w 3
c 1

I need a formula that calculates how many of the w's have numbers greater
than 1. I was trying this formula:
sumproduct(--(C2:C12="W"),--(D2:D121))
but it doesn't work.

Double count
 

As a followup, put this formula in E2: =LEN(C2) and drag down through E12.
If you have only 1 character in each cell in C2:C12, the formula will
compute 1 as the answer. 2 if you have character + space, 3 if you have
character + space + space etc.

Tyro


"Tyro" wrote in message
...
You may have spaces after the letters in C2:C12. "w " is not equal to
"w".

Tyro

"Byron720" wrote in message
...
I have the following data:

w 1
w 1
w 2
w 1
w 1
c 3
w 2
c 1
c 1
w 3
c 1

I need a formula that calculates how many of the w's have numbers greater
than 1. I was trying this formula:
sumproduct(--(C2:C12="W"),--(D2:D121))
but it doesn't work.