Excel, two text fields equal the number 1 per occurance

  • Thread starter Thread starter Don McLean
  • Start date Start date
D

Don McLean

I am in need of help with an excel function. Here is some sample data.

Don coffee
James coffee
Joe tea
Sally coffee
Don tea
Sally tea
Sally tea
Don coffee

I need these two columns to be conditional in that If Don drinks tea that
equals one occurance. The data I need would show that Don drinks tea "1", Don
drinks coffee "2". Sally drinks tea "2", Sally drinks coffee "1".

Can anyone please help me? Thanks.
 
Assume the data range is A1:B100 with headers in Row1.

With the query name in C2 and item in D2 try the below formula

=SUMPRODUCT(--(A2:A100=C2),--(B2:B100=D2))
 
Assume the data range is A1:B100 with headers in Row1.

With the query name in C2 and item in D2 try the below formula

=SUMPRODUCT(--(A2:A100=C2),--(B2:B100=D2))
 
I recieve a #NUM! error

Jacob Skaria said:
Assume the data range is A1:B100 with headers in Row1.

With the query name in C2 and item in D2 try the below formula

=SUMPRODUCT(--(A2:A100=C2),--(B2:B100=D2))
Click to expand...
 
I recieve a #NUM! error

Jacob Skaria said:
Assume the data range is A1:B100 with headers in Row1.

With the query name in C2 and item in D2 try the below formula

=SUMPRODUCT(--(A2:A100=C2),--(B2:B100=D2))
Click to expand...
 
I basically need the
=countif(range, "text") function that is conditional on whether or not
another column has specific text.

Thanks. ~Don
 
I basically need the
=countif(range, "text") function that is conditional on whether or not
another column has specific text.

Thanks. ~Don
 
I just found out that this can be done with the COUNTIFS function in Excel
2007. But how can we do it in 2003?
 
I just found out that this can be done with the COUNTIFS function in Excel
2007. But how can we do it in 2003?
 
Since you can't use COUNTIFS in 2003 and you have two criteria in your
counting decision, you could concatenate your two criteria in column C
and use that column in your COUNTIF formula. I would put put the
following formula in column C

=A4&" "&B4

and this formula in column D

=COUNTIF($C$4:$C$11,C4)

copy them down. (my data was in rows 4:11)

You could use an Advanced Filter to make a unique list of name-drink
combinations in another location, and then use the countif formula for
those cells.

Good luck

Ken
Norfolk, Va
 
Since you can't use COUNTIFS in 2003 and you have two criteria in your
counting decision, you could concatenate your two criteria in column C
and use that column in your COUNTIF formula. I would put put the
following formula in column C

=A4&" "&B4

and this formula in column D

=COUNTIF($C$4:$C$11,C4)

copy them down. (my data was in rows 4:11)

You could use an Advanced Filter to make a unique list of name-drink
combinations in another location, and then use the countif formula for
those cells.

Good luck

Ken
Norfolk, Va
 
You can't use entire columns as range references unless you're using Excel
2007. If you are using Excel 2007 the COUNTIFS function would be a better
choice.

So, unless you're using Excel 2007 use a smaller specific range.
 
You can't use entire columns as range references unless you're using Excel
2007. If you are using Excel 2007 the COUNTIFS function would be a better
choice.

So, unless you're using Excel 2007 use a smaller specific range.
 
Back
Top