Calculating combination of text occurances
 

HI,

I am attempting to update our medical benefits spreadsheet. I would like to
calculate how many employees are enrolled in each plan type. Column A lists
the provider (Aetna, Kaiser and Blue Shield), Column B lists the coverage
type (employee, emp + spouse etc). I wanted a total for each possible
combination from both colums.
For example:
Kaiser+Employee
Kaiser+Employee/Spouse
Aetna+Employee

I have failed miserably with the COUNTIF and hope someone out there will
take pity on me and show me the light.
Thanks,
AGA

Calculating combination of text occurances
 

Hi,

Try this

=SUMPRODUCT((A1:A22="Kaiser")*(B1:B22="Employee"))

In practice i'd use cell references in the formula

=SUMPRODUCT((A1:A22=C1)*(B1:B22=C2))


Mike

"AGA" wrote:

HI,

I am attempting to update our medical benefits spreadsheet. I would like to
calculate how many employees are enrolled in each plan type. Column A lists
the provider (Aetna, Kaiser and Blue Shield), Column B lists the coverage
type (employee, emp + spouse etc). I wanted a total for each possible
combination from both colums.
For example:
Kaiser+Employee
Kaiser+Employee/Spouse
Aetna+Employee

I have failed miserably with the COUNTIF and hope someone out there will
take pity on me and show me the light.
Thanks,
AGA

Calculating combination of text occurances
 

Use =SUMPRODUCT()

=SUMPRODUCT((A1:A1000="Aetna")*(B1:B1000="employee "))

and similar formulas for the other 5 combinations
--
Gary''s Student - gsnu200838


"AGA" wrote:

HI,

I am attempting to update our medical benefits spreadsheet. I would like to
calculate how many employees are enrolled in each plan type. Column A lists
the provider (Aetna, Kaiser and Blue Shield), Column B lists the coverage
type (employee, emp + spouse etc). I wanted a total for each possible
combination from both colums.
For example:
Kaiser+Employee
Kaiser+Employee/Spouse
Aetna+Employee

I have failed miserably with the COUNTIF and hope someone out there will
take pity on me and show me the light.
Thanks,
AGA

Calculating combination of text occurances
 

THANK YOU!!!

"Mike H" wrote:

Hi,

Try this

=SUMPRODUCT((A1:A22="Kaiser")*(B1:B22="Employee"))

In practice i'd use cell references in the formula

=SUMPRODUCT((A1:A22=C1)*(B1:B22=C2))


Mike

"AGA" wrote:

HI,

I am attempting to update our medical benefits spreadsheet. I would like to
calculate how many employees are enrolled in each plan type. Column A lists
the provider (Aetna, Kaiser and Blue Shield), Column B lists the coverage
type (employee, emp + spouse etc). I wanted a total for each possible
combination from both colums.
For example:
Kaiser+Employee
Kaiser+Employee/Spouse
Aetna+Employee

I have failed miserably with the COUNTIF and hope someone out there will
take pity on me and show me the light.
Thanks,
AGA

Calculating combination of text occurances
 

THANK YOU!!!

"Gary''s Student" wrote:

Use =SUMPRODUCT()

=SUMPRODUCT((A1:A1000="Aetna")*(B1:B1000="employee "))

and similar formulas for the other 5 combinations
--
Gary''s Student - gsnu200838


"AGA" wrote:

HI,

I am attempting to update our medical benefits spreadsheet. I would like to
calculate how many employees are enrolled in each plan type. Column A lists
the provider (Aetna, Kaiser and Blue Shield), Column B lists the coverage
type (employee, emp + spouse etc). I wanted a total for each possible
combination from both colums.
For example:
Kaiser+Employee
Kaiser+Employee/Spouse
Aetna+Employee

I have failed miserably with the COUNTIF and hope someone out there will
take pity on me and show me the light.
Thanks,
AGA

Calculating combination of text occurances
 

Hi,

You may also consider using a pivot table. It get you the result without
any formulas

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"AGA" wrote in message
...
HI,

I am attempting to update our medical benefits spreadsheet. I would like
to
calculate how many employees are enrolled in each plan type. Column A
lists
the provider (Aetna, Kaiser and Blue Shield), Column B lists the coverage
type (employee, emp + spouse etc). I wanted a total for each possible
combination from both colums.
For example:
Kaiser+Employee
Kaiser+Employee/Spouse
Aetna+Employee

I have failed miserably with the COUNTIF and hope someone out there will
take pity on me and show me the light.
Thanks,
AGA