Calculating combination of text occurances

[AGA]

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

 

[Mike H]

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

 

[Gary''s Student]

Use =SUMPRODUCT()

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

and similar formulas for the other 5 combinations

 

[AGA]

THANK YOU!!!

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]

THANK YOU!!!

Use =SUMPRODUCT()

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

and similar formulas for the other 5 combinations

 

[Ashish Mathur]

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