Excel, two text fields equal the number 1 per occurance
 

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.

Excel, two text fields equal the number 1 per occurance
 

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))
--
If this post helps click Yes
---------------
Jacob Skaria


"Don McLean" wrote:

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.

Excel, two text fields equal the number 1 per occurance
 

I recieve a #NUM! error

"Jacob Skaria" wrote:

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))
--
If this post helps click Yes
---------------
Jacob Skaria


"Don McLean" wrote:

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.

Excel, two text fields equal the number 1 per occurance
 

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

Thanks. ~Don

"Jacob Skaria" wrote:

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))
--
If this post helps click Yes
---------------
Jacob Skaria


"Don McLean" wrote:

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.

Excel, two text fields equal the number 1 per occurance
 

http://www.contextures.com/xlFunctio...tml#SumProduct

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

Thanks. ~Don

"Jacob Skaria" wrote:

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))
--
If this post helps click Yes
---------------
Jacob Skaria


"Don McLean" wrote:

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.

Excel, two text fields equal the number 1 per occurance
 

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" wrote:

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.

Excel, two text fields equal the number 1 per occurance
 

Doesn't help, any other ideas?

"Glenn" wrote:

http://www.contextures.com/xlFunctio...tml#SumProduct

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

Thanks. ~Don

"Jacob Skaria" wrote:

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))
--
If this post helps click Yes
---------------
Jacob Skaria


"Don McLean" wrote:

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.

Excel, two text fields equal the number 1 per occurance
 

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

Don McLean wrote:
Doesn't help, any other ideas?

"Glenn" wrote:

http://www.contextures.com/xlFunctio...tml#SumProduct

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

Thanks. ~Don

"Jacob Skaria" wrote:

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))
--
If this post helps click Yes
---------------
Jacob Skaria


"Don McLean" wrote:

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.

Excel, two text fields equal the number 1 per occurance
 

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

On May 13, 3:49*pm, Don McLean
wrote:
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" wrote:
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.- Hide quoted text -

- Show quoted text -

Excel, two text fields equal the number 1 per occurance
 

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.

--
Biff
Microsoft Excel MVP


"Don McLean" wrote in message
...
Doesn't help, any other ideas?

"Glenn" wrote:

http://www.contextures.com/xlFunctio...tml#SumProduct

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

Thanks. ~Don

"Jacob Skaria" wrote:

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))
--
If this post helps click Yes
---------------
Jacob Skaria


"Don McLean" wrote:

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.

Excel, two text fields equal the number 1 per occurance
 

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

On May 13, 4:04*pm, Glenn wrote:
Describes exactly what you want to do. *How does that not help?



Don McLean wrote:
Doesn't help, any other ideas?

"Glenn" wrote:

http://www.contextures.com/xlFunctio...tml#SumProduct

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

Thanks. ~Don

"Jacob Skaria" wrote:

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))
--
If this post helps click Yes
---------------
Jacob Skaria

"Don McLean" wrote:

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.- Hide quoted text -

- Show quoted text -

Excel, two text fields equal the number 1 per occurance
 

Don McLean wrote...
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?

"Jacob Skaria" wrote:
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))
....