COUNTIF or SUMPRODUCT?
 

First of all, I would like to say I have found a lot of questions & answers
that have become useful to me in my day to day excel experience. Thanks to
all the posters and the intelligent folks that have the answers! My question
is... I have 2 columns of data, column A A1:A100 =RC44, column B (need to be
less < than 74) count as 1
EXAMPLE:
Column A Column B
RC44 60
RC44 77
RC52 14
RC60 58
RC44 72

If coumn A = RC44 & column B is less than 74 should =1 (or example above is=
2)
Can anyone provide any help?

COUNTIF or SUMPRODUCT?
 

=sumproduct(--(a1:a100="rc44"),--(b1:b100<74))

Adjust the range, but you can't use the whole column.

Scott wrote:

First of all, I would like to say I have found a lot of questions & answers
that have become useful to me in my day to day excel experience. Thanks to
all the posters and the intelligent folks that have the answers! My question
is... I have 2 columns of data, column A A1:A100 =RC44, column B (need to be
less < than 74) count as 1
EXAMPLE:
Column A Column B
RC44 60
RC44 77
RC52 14
RC60 58
RC44 72

If coumn A = RC44 & column B is less than 74 should =1 (or example above is=
2)
Can anyone provide any help?

--

Dave Peterson

COUNTIF or SUMPRODUCT?
 

=SUMPRODUCT(--(A1:A100="RC44"),--(B1:B100<74))

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


"Scott" wrote in message
...
First of all, I would like to say I have found a lot of questions &
answers
that have become useful to me in my day to day excel experience. Thanks to
all the posters and the intelligent folks that have the answers! My
question
is... I have 2 columns of data, column A A1:A100 =RC44, column B (need to
be
less < than 74) count as 1
EXAMPLE:
Column A Column B
RC44 60
RC44 77
RC52 14
RC60 58
RC44 72

If coumn A = RC44 & column B is less than 74 should =1 (or example above
is=
2)
Can anyone provide any help?

COUNTIF or SUMPRODUCT?
 

Thanks for the information, works perfectly!!!

"Peo Sjoblom" wrote:

=SUMPRODUCT(--(A1:A100="RC44"),--(B1:B100<74))

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


"Scott" wrote in message
...
First of all, I would like to say I have found a lot of questions &
answers
that have become useful to me in my day to day excel experience. Thanks to
all the posters and the intelligent folks that have the answers! My
question
is... I have 2 columns of data, column A A1:A100 =RC44, column B (need to
be
less < than 74) count as 1
EXAMPLE:
Column A Column B
RC44 60
RC44 77
RC52 14
RC60 58
RC44 72

If coumn A = RC44 & column B is less than 74 should =1 (or example above
is=
2)
Can anyone provide any help?

COUNTIF or SUMPRODUCT?
 

Try

=SUMPRODUCT((A2:A6="RC44")*(B2:B6<74))
if the value comes as #value click F2 and hit control+shift+enter

based on your five sets of values(a2 to a6 and b2 to b6). check whether
there are any nonprintable characters in your cell entreis.


"Scott" wrote in message
...
First of all, I would like to say I have found a lot of questions &
answers
that have become useful to me in my day to day excel experience. Thanks to
all the posters and the intelligent folks that have the answers! My
question
is... I have 2 columns of data, column A A1:A100 =RC44, column B (need to
be
less < than 74) count as 1
EXAMPLE:
Column A Column B
RC44 60
RC44 77
RC52 14
RC60 58
RC44 72

If coumn A = RC44 & column B is less than 74 should =1 (or example above
is=
2)
Can anyone provide any help?