I have a spreadsheet and column C has words like suspended, authorised,
pending etc - can I create a formula to total the different states?

=COUNTIF(C:C,"*suspend*")
=COUNTIF(C:C,"*auth*")
=COUNTIF(C:C,"*pendin*")

--
If this post helps click Yes
---------------
Jacob Skaria


"Dottled" wrote:

I have a spreadsheet and column C has words like suspended, authorised,
pending etc - can I create a formula to total the different states?

use =sumif, with "range" as C column and "sum range" as the numbers and
criteria is the word you want to sum for.

"Dottled" wrote:

I have a spreadsheet and column C has words like suspended, authorised,
pending etc - can I create a formula to total the different states?

Hi
Let's say your data to summarize is in column B . create a list of the
different stages in column D starting in row 1 and in E1 enter the formula as
follow

=sumproduct(--(D1=$C$1:$C$100),$B$1:$B$100), change range to fit your needs

if you are using excel 2007 use

=sumproduct(--(D1=C:C),B:B)

if this was helpful please click yes, thanks

"Dottled" wrote:

I have a spreadsheet and column C has words like suspended, authorised,
pending etc - can I create a formula to total the different states?

to count the total of occurence of each stage
use =COUNTIF(C2:C10,"Suspend") or
use a cell to hold the criteria, eg D2
=COUNTIF(C2:C10,D2)

to sum the values on on another column eg Col. B based on these conditions
use =SUMIF(C2:C10,"suspend",B1:B9) or
use a cell to hold the condition, eg D2
=SUMIF(C1:C9,D2,B1:B9)

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Dottled" wrote:

I have a spreadsheet and column C has words like suspended, authorised,
pending etc - can I create a formula to total the different states?

Thank you for being an ordinary user and helping me - as you probably know if
you don't use software for a long time you forget and it doesn't help that
i'm a self taught user, so people like you who are so important to other
ordinary users.

"Francis" wrote:

to count the total of occurence of each stage
use =COUNTIF(C2:C10,"Suspend") or
use a cell to hold the criteria, eg D2
=COUNTIF(C2:C10,D2)

to sum the values on on another column eg Col. B based on these conditions
use =SUMIF(C2:C10,"suspend",B1:B9) or
use a cell to hold the condition, eg D2
=SUMIF(C1:C9,D2,B1:B9)

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Dottled" wrote:

I have a spreadsheet and column C has words like suspended, authorised,
pending etc - can I create a formula to total the different states?