How to Count Rows with defined values in multiple columns
 

I need a formula or function that will sum the number of occurences a
specific text string is entered into cells in column B. I only want to count
the occurences when a number that is in column A (on the same row as the
text) is equal to a specific number. Sounds easy but it's very frustrating.

How to Count Rows with defined values in multiple columns
 

"ryesworld" wrote in message
...
I need a formula or function that will sum the number of occurences a
specific text string is entered into cells in column B. I only want to
count
the occurences when a number that is in column A (on the same row as the
text) is equal to a specific number. Sounds easy but it's very
frustrating.

=SUMPRODUCT(--(AA1:AA6=YourNumber),--(AB1:AB6="YourString"))

Ciao
Bruno

How to Count Rows with defined values in multiple columns
 

With the text to look up entered in D1,
And the specific number to use in C1, try this:

=SUMPRODUCT((A1:A20=C1)*(B1:B20=D1))

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"ryesworld" wrote in message
...
I need a formula or function that will sum the number of occurences a
specific text string is entered into cells in column B. I only want to
count
the occurences when a number that is in column A (on the same row as the
text) is equal to a specific number. Sounds easy but it's very
frustrating.

How to Count Rows with defined values in multiple columns
 

Right idea but it gave me a "#NUM!" error. I think this formula must only be
for numbers. I need to count the number of times a string of text is entered
- when it's on the same row as a specific number.

"Bruno Campanini" wrote:

"ryesworld" wrote in message
...
I need a formula or function that will sum the number of occurences a
specific text string is entered into cells in column B. I only want to
count
the occurences when a number that is in column A (on the same row as the
text) is equal to a specific number. Sounds easy but it's very
frustrating.

=SUMPRODUCT(--(AA1:AA6=YourNumber),--(AB1:AB6="YourString"))

Ciao
Bruno

How to Count Rows with defined values in multiple columns
 

"RagDyer" wrote in message
...
With the text to look up entered in D1,
And the specific number to use in C1, try this:

=SUMPRODUCT((A1:A20=C1)*(B1:B20=D1))

You are right my friend!
Using "*" instead of "," the formula becomes
four-byte shorter.

Bruno

How to Count Rows with defined values in multiple columns
 

"ryesworld" wrote in message
...
Right idea but it gave me a "#NUM!" error. I think this formula must only
be
for numbers. I need to count the number of times a string of text is
entered
- when it's on the same row as a specific number.

Sorry ryesworld,

1 ab
2 bc
3 bc
1 ad
3 bc
3 ad

=SUMPRODUCT(--(AA1:AA6=3),--(AB1:AB6="bc"))
gives 2.

The result is correct.
Is it not?

Bruno

How to Count Rows with defined values in multiple columns
 

YES, that works, unless the two ranges you are searching (A1:A20 & B1:B20)
are on a separate sheet... that produces a #NAME? error. Any ideas?

"RagDyer" wrote:

With the text to look up entered in D1,
And the specific number to use in C1, try this:

=SUMPRODUCT((A1:A20=C1)*(B1:B20=D1))

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"ryesworld" wrote in message
...
I need a formula or function that will sum the number of occurences a
specific text string is entered into cells in column B. I only want to
count
the occurences when a number that is in column A (on the same row as the
text) is equal to a specific number. Sounds easy but it's very
frustrating.

How to Count Rows with defined values in multiple columns
 

Sorry Bruno, Your Formula does work, but not for my situation... the two
lookup ranges (AA1:AA6 & AB1:AB6) are on a separate sheet. This produces a
#VALUE! error. Any Ideas? (Also, it's strange that the formula doesn't work
at all if a lookup range is an entire column, ie: AA:AA)

"Bruno Campanini" wrote:

"ryesworld" wrote in message
...
Right idea but it gave me a "#NUM!" error. I think this formula must only
be
for numbers. I need to count the number of times a string of text is
entered
- when it's on the same row as a specific number.

Sorry ryesworld,

1 ab
2 bc
3 bc
1 ad
3 bc
3 ad

=SUMPRODUCT(--(AA1:AA6=3),--(AB1:AB6="bc"))
gives 2.

The result is correct.
Is it not?

Bruno

How to Count Rows with defined values in multiple columns
 

Do you mean something like this:

=SUMPRODUCT((Sheet2!A1:A20=C1)*(Sheet1!B1:B20=D1))
?
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"ryesworld" wrote in message
...
YES, that works, unless the two ranges you are searching (A1:A20 & B1:B20)
are on a separate sheet... that produces a #NAME? error. Any ideas?

"RagDyer" wrote:

With the text to look up entered in D1,
And the specific number to use in C1, try this:

=SUMPRODUCT((A1:A20=C1)*(B1:B20=D1))

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"ryesworld" wrote in message
...
I need a formula or function that will sum the number of occurences a
specific text string is entered into cells in column B. I only want to
count
the occurences when a number that is in column A (on the same row as the
text) is equal to a specific number. Sounds easy but it's very
frustrating.

How to Count Rows with defined values in multiple columns
 

Yes, thank you! The sheet reference works now, I don't see how yours was
different from mine, except that it works!

"RagDyeR" wrote:

Do you mean something like this:

=SUMPRODUCT((Sheet2!A1:A20=C1)*(Sheet1!B1:B20=D1))
?
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"ryesworld" wrote in message
...
YES, that works, unless the two ranges you are searching (A1:A20 & B1:B20)
are on a separate sheet... that produces a #NAME? error. Any ideas?

"RagDyer" wrote:

With the text to look up entered in D1,
And the specific number to use in C1, try this:

=SUMPRODUCT((A1:A20=C1)*(B1:B20=D1))

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"ryesworld" wrote in message
...
I need a formula or function that will sum the number of occurences a
specific text string is entered into cells in column B. I only want to
count
the occurences when a number that is in column A (on the same row as the
text) is equal to a specific number. Sounds easy but it's very
frustrating.