Find total of cells with the same string

[cpliu]

How do I find the total of cells in a column with the same name? To make
it more complex, how do I add the value of another column in deciding the
total?

For example

Person A, admin, active
Person B, software dev, inactive
Person C, software dev, active
Person D, software dev, active
Person E, admin, active

"," is the divider for each cell

1) I'd like to get 3 for total of cells with software dev
2) I'd like to get 2 for total of active software dev

How can I perform these tasks?

Thanks for the help,


cpliu

 

[Frank Kabel]

Hi
try the following:
1) =COUNTIF(B:B,"software dev")

2) =SUMPRODUCT((B1:B100="software dev")*(C1:C100="active"))

 

[Ken Wright]

Take a look at COUNTIF which allows you to specify a range and then a criteria
for the cells you would like to count in they meet that criteria.

=COUNTIF(range,criteria)
=COUNTIF(A3:A30,"Software Dev")

Also, take a look at SUMIF which allows you to specify a range and then a
criteria for the cells you would like to count in they meet that criteria, and
then another range to sum, assuming that the row in the first range met the
criteria.

=SUMIF(range,criteria,sum_range)
=SUMIF(A3:A30,"Software Dev",B3:B30)

 

[cpliu]

Thank you all for the great help. It works now.

How can I add OR in the criteria? eg. "software dev" or "software" I tried
adding something like(B1:B100="software") and adding OR in between but both
generate an eror.


Thanks again for the help,


cpliu

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(((B1:B100="software
dev")+(B1:B100="software"))*(C1:C100="active"))

 

[cpliu]

Hi
try
=SUMPRODUCT(((B1:B100="software
dev")+(B1:B100="software"))*(C1:C100="active"))

Frank,
Thanks again for the help. It works now.

cpliu

 

[cpliu]

To expand on the same function solution provided by Frank:
I'd like to add one more criterium- date. I'd like to find the total of
of active software dev in "2004".

I have:
=SUMPRODUCT((E2:E714="Software dev")*(F2:F714="active")+
(L2:L712>"12/31/2003"))
but it came up as #N/A.

So, how can I add the extra criterium? Excel Help does not provide that
complexity I need.

Thanks a lot for the help,

cpliu

 

[cpliu]

=COUNTIF(range,criteria)
=COUNTIF(A3:A30,"Software Dev")

=SUMIF(range,criteria,sum_range)
=SUMIF(A3:A30,"Software Dev",B3:B30)

Ken, thanks for the instruction. SUMIF and COUNTIF cannot add more criteria
like SUMPRODUCT, right? I tried it and it works great to count the total of
cells with the same string.

cpliu

 

[Frank Kabel]

Hi
- first all ranges must have the same dimension: so it has to be
L2:L714

Try
=SUMPRODUCT((E2:E714="Software
dev")*(F2:F714="active")*(YEAR(L2:L714)=2004))

 

[AlfD]

Hi!

You could expand the second criterion.
Use the formulation AND (First criterion, second criterion).

Alf

 

[Ken Wright]

As advised, ranges MUST be the same length for ALL ranges, but you also need to
change the reference to a date in your formula. It is looking at that date you
supplied as a text string and no more. If you want it to be evaluated as a date
then try wrapping it with DATEVALUE which takes a text string that looks like a
date anf converst sit to a real one, eg:-

=SUMPRODUCT(--(A1:A1000>DATEVALUE("23/03/2004")))

As far as expanding, how far do you want to go - Just keep adding them:-

=SUMPRODUCT((Rng1="abc")*((Rng2="def")+(Rng3="ghi"))*(Rng4="jkl")*(Rng5="mno")*(
Rng6="pqr1"))

and so on