# COUNT FORMULA QUERY

#### broncos347

##### Active Member
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.

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Something like

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

?

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.

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

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?

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

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

Hi,

Try:

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

Hi Fairwinds,

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

I have three at the moment.

For the count of all three:

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

Replies
22
Views
458
Replies
5
Views
208
Replies
6
Views
70
Replies
9
Views
381
Replies
11
Views
444

1,203,677
Messages
6,056,685
Members
444,883
Latest member
garyarubin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back