Counting paired data

[Ken Schmidt]

I have a set of data with columns alternating names and numbers. I would
like to return the number of times a specified name and number is paired up.
For example, how many times is "Jones" in the data range with "30" in the
cell to the right. I have tried OFFSET, COUNTIF, and SUMPRODUCT formulas,
but have been unable to get the results I want. Any help would be
appreciated. An array formula would be fine. Using Excel 97.

Ken Schmidt

 

[papou]

Hi Ken
Provided names are in column A and numbers in column B, formula:
=SUMPRODUCT((A1:A50="Jones")*(B1:B50=30))

NB: This is NOT an array formula

HTH
Cordially
Pascal

 

[Ken Schmidt]

Thanks for the reply. I should have made clearer that I have several
columns of data like this; column C is names, column D numbers, column E
names again, etc. I need to know whenever "Jones" appears in any names
column, how many times "30" is in the cell to the right. I thought maybe I
could use OFFSET to shift the search over a column, but can't get that to
work.

Ken

 

[RagDyeR]

It really doesn't matter how many columns you have, as long as they follow
the same pattern of names and numbers.

Try this for a 10 column data list:

=SUMPRODUCT((A1:I100="Jones")*(B1:J100=30))
--

HTH,

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


Thanks for the reply. I should have made clearer that I have several
columns of data like this; column C is names, column D numbers, column E
names again, etc. I need to know whenever "Jones" appears in any names
column, how many times "30" is in the cell to the right. I thought maybe I
could use OFFSET to shift the search over a column, but can't get that to
work.

Ken

 

[kitkat]

I just tried to use that, and it's not working.

=SUMPROD((D212107="CompanyXYZ")*(F2:F12107="CompanyXYZ")*(H2:H12107="Service")*(Q2:Q12107))

I get #N/A. I tried putting "CompanyXYZ" into Cell A12111 an
"Service" into Cell B12111, and neither of these worked, either.

=SUMPROD((D212107=$A$12111)*(F2:F12107=$A$12111)*(H2:H12107=$B$12111)*(Q2:Q12107))
=SUMPROD((D212107=A12111)*(F2:F12107=A12111)*(H2:H12107=B12111)*(Q2:Q12107)

 

[Ken Schmidt]

I tried your suggestion and it worked! Thank a lot.

Ken

 

[RagDyer]

Thanks for the feed-back.
--


Regards,

RD