Count number of times two columns have desired values

I have two adjacent columns, one with cell values of either 20 or 40, the
other with values of "H", "R" or "D"

What I want to do is go through and compare the cells of each column, like
B1 to C1, B2 to C2 etc.

I want to count how often B# is 40 and C# is "R", B# is 40 and C# is "H" etc.

I've used countless COUNTIF statements and IF statements and COUNT
statements... I just can't seem to get a number above 1!

=SUMPRODUCT((B1:B1000=40)*C1:C1000="R))

HTH
--
AP

"Gavin Deveau" <[email protected]> a écrit dans le
message de news: [email protected]...
>I have two adjacent columns, one with cell values of either 20 or 40, the
> other with values of "H", "R" or "D"
>
> What I want to do is go through and compare the cells of each column, like
> B1 to C1, B2 to C2 etc.
>
> I want to count how often B# is 40 and C# is "R", B# is 40 and C# is "H"
> etc.
>
> I've used countless COUNTIF statements and IF statements and COUNT
> statements... I just can't seem to get a number above 1!

PivotTable

If you've never done one, use the wizard. Drag your number column to the
top (drop column fields here), and your letter column to the left (drop row
fields here). Then drag your letter column into the center of the table
(drop data columns here), and summarize by COUNT, which it should do by
default since you're summarizing the letter column.

"Gavin Deveau" <[email protected]> wrote in message
news:[email protected]...
>I have two adjacent columns, one with cell values of either 20 or 40, the
> other with values of "H", "R" or "D"
>
> What I want to do is go through and compare the cells of each column, like
> B1 to C1, B2 to C2 etc.
>
> I want to count how often B# is 40 and C# is "R", B# is 40 and C# is "H"
> etc.
>
> I've used countless COUNTIF statements and IF statements and COUNT
> statements... I just can't seem to get a number above 1!