Excel, two text fields equal the number 1 per occurance

[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.

 

[Jacob Skaria]

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))

 

[Don McLean]

I recieve a #NUM! error

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))

 

[Don McLean]

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

Thanks. ~Don

 

[Glenn]

http://www.contextures.com/xlFunctions04.html#SumProduct

 

[Don McLean]

I just found out that this can be done with the COUNTIFS function in Excel
2007. But how can we do it in 2003?

 

[Don McLean]

Doesn't help, any other ideas?

http://www.contextures.com/xlFunctions04.html#SumProduct

 

[Glenn]

Describes exactly what you want to do. How does that not help?

 

[krcowen]

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

 

[T. Valko]

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.

 

[krcowen]

Hard to believe that link didn't take care of the problem.
Ken

 

[Harlan Grove]

I recieve a #NUM! error

Then you're not using Jacob's formula OR you have a #NUM! error in one
of your cells. What's the formula you're actually using?
....