COUNT FORMULA QUERY

[broncos347]

I have a spreadsheet with a series of columns and I would like to count up the cells that have information in them, but only if a certain condition is met.

This condition is, if the cells in column AS contain either of the following words "contractor 1", "contractor 2" or "tbc" then I want to count up the number of cells that have been allocated to one of the contractors or have yet to be confirmed.

The cells that I need to count up could contain either a date, a number or some text.

 

[SteveO59L]

Something like

=COUNTIF(H1:H39,"tba")+COUNTIF(H1:H39,"contractor1")

?

 

[broncos347]

Nearly, I have say column AS with the text "contractor 1" etc. and I have column AW with either text, a number or a date and what I would like to do is count up all of the cells with either text, a number or a date allocated to contractor 1, and so on.

Hope this makes sense.

 

[SteveO59L]

Ok, in a spare column to the right of your data (say az),

=IF(AND(as2="contractor1",aw2<>""),as2,"")

will set cells to the value contractor1, where as="Contractor1" and aw isnt blank.

Then at the buttom of your range, list the values you want to be counted, and use

=COUNTIF(az2:az29,as34)

to count the values

 

[broncos347]

Thanks. It works

The only problem I've got is that my spreadsheet has around 50 columns with various text/dates, etc in that I need to count up for a client.

Is there an easier way to count up the columns without having to put in 50 extra columns?

 

[broncos347]

Sorry, how do I make this formula work if I have more than one contractor? I have three contractors in total.

 

[SteveO59L]

You wouldnt need to add 50 columns, just specify the count criteria (Contractor1, Contractor2 etc)

 

[fairwinds]

Hi,

Try:

=SUMPRODUCT(--(AS1:AS10="contractor 1"),--(AW1:AW10<>""))

 

[broncos347]

Hi Fairwinds,

Would I be able to use this formula if I have more than one contractor?

I have three at the moment.

 

[fairwinds]

For the count of all three:

=SUMPRODUCT((AS1:AS10={"contractor 1","contractor 2","tbc"})*(AW1:AW10<>""))