|
|
question about SUMPRODUCT |
|
Author |
Message |
JohnMcCombe
|
Posted: Fri Nov 04 15:09:45 CST 2005 |
Top |
worksheet functions >> question about SUMPRODUCT
I have a worksheet laid out like this
COLUMN A COLUMN B
manager present
supervisor absent
supervisor present
CEO absent
manager absent
How would I write a formula that would count every "manager" and
"supervisor" present?
thanks.
Excel233
|
|
|
|
|
Domenic
|
Posted: Fri Nov 04 15:09:45 CST 2005 |
Top |
worksheet functions >> question about SUMPRODUCT
Try...
=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A5,{"Manager","Supervisor"},0))),--(B1:B
5="Present"))
or
=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A5,D1:D2,0))),--(B1:B5=E1))
...where D1:D2 contain Manager and Supervisor, and E1 contains Present.
Hope this helps!
> I have a worksheet laid out like this
>
> COLUMN A COLUMN B
> manager present
> supervisor absent
> supervisor present
> CEO absent
> manager absent
>
> How would I write a formula that would count every "manager" and
> "supervisor" present?
>
> thanks.
|
|
|
|
|
Bernard
|
Posted: Fri Nov 04 15:10:09 CST 2005 |
Top |
worksheet functions >> question about SUMPRODUCT
Try =SUMPRODUCT(--(A1:A100="manager"),--(B1:B100="supervisor"))
The double negatives convert FALSE/TRUE to 0/1 to allow arithmetic to work
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
>I have a worksheet laid out like this
>
> COLUMN A COLUMN B
> manager present
> supervisor absent
> supervisor present
> CEO absent
> manager absent
>
> How would I write a formula that would count every "manager" and
> "supervisor" present?
>
> thanks.
|
|
|
|
|
Peo
|
Posted: Fri Nov 04 15:11:05 CST 2005 |
Top |
worksheet functions >> question about SUMPRODUCT
Assuming "and" means or in this case
=SUMPRODUCT((A1:A5={"manager","supervisor"})*(B1:B5="present"))
--
Regards,
Peo Sjoblom
> I have a worksheet laid out like this
>
> COLUMN A COLUMN B
> manager present
> supervisor absent
> supervisor present
> CEO absent
> manager absent
>
> How would I write a formula that would count every "manager" and
> "supervisor" present?
>
> thanks.
|
|
|
|
|
nmc1104
|
Posted: Fri Nov 04 15:32:06 CST 2005 |
Top |
worksheet functions >> question about SUMPRODUCT
thanks! you guys are pros.
> Assuming "and" means or in this case
>
> =SUMPRODUCT((A1:A5={"manager","supervisor"})*(B1:B5="present"))
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> > I have a worksheet laid out like this
> >
> > COLUMN A COLUMN B
> > manager present
> > supervisor absent
> > supervisor present
> > CEO absent
> > manager absent
> >
> > How would I write a formula that would count every "manager" and
> > "supervisor" present?
> >
> > thanks.
>
>
>
|
|
|
|
|
|
|