How to Count Rows with defined values in multiple columns

[ryesworld]

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.

[Bruno Campanini]

"ryesworld" <[email protected]> wrote in message
news:[email protected]...
>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

[RagDyer]

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

[ryesworld]

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" <[email protected]> wrote in message
> news:[email protected]...
> >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
>
>
>

[Bruno Campanini]

"RagDyer" <[email protected]> wrote in message
news:[email protected]...
> 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

[Bruno Campanini]

"ryesworld" <[email protected]> wrote in message
news:[email protected]...
> 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

[ryesworld]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>

[ryesworld]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[RagDyeR]

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" <[email protected]> wrote in message
news:[email protected]...
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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>

[ryesworld]

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" <[email protected]> wrote in message
> news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> >
> >
>
>
>