Additional Column Adding Query

[broncos347]

I have two columns (AS and BV).

What I would like to do is to total up the number of sites that are asigned to a number of contractors (A, B and C).

The contractors are listed in AS and the data I would like to add up is in BV.

What I am after is a total number (not sum) for each contractor. I have started to try countif but I can't seem to make it work.

Regards,

Ian.

 

[Von Pookie]

Do you mean you want to add the number of sites for each contractor? I think this works:

Book1
	A	B	C	D	E
1	Contractor	# of Sites		Contractor	Total Sites
2	A	3		A	6
3	B	5		B	18
4	B	2		C	3
5	B	4
6	B	6
7	A	1
8	A	2
9	B	1
10	C	3
Sheet1

 

[broncos347]

Nearly, but not quite.

The column I am using (BV) contains costs and what I want to do is to find out how many sites have been quoted by each contractor.

Is this possible?

 

[Von Pookie]

Then couldn't you just count the number of times the contractor name appears instead of dealing with the costs column?

Using the same setup as in my previous post, use this formula instead:
=COUNTIF($A$2:$A$10,$D2)

and copy the formula down.

The results I get are
A=3
B=5
C=1

 

[broncos347]

ADVERTISEMENT

Unfortunately, the column I am trying to use contains costs and are therefore different.

What I am trying to find out is, out of say 200 sites how many has contractor A costed and how many has contractor B costed.

So rather than "sum" the column BV I would like to be able to just count up the number of cells in the column that contain data.

 

[Von Pookie]

What I am trying to find out is, out of say 200 sites how many has contractor A costed and how many has contractor B costed.

So rather than "sum" the column BV I would like to be able to just count up the number of cells in the column that contain data.

Riiiiight....and what the last formula I posted does is count the number of times each contractor name appears in column A--which is one time for each cost listed in column B. So I just counted the number of times the contractor name appears.

It should work just the same

Unless, of course, you have listed the contractors in column A and there is nothing in column B for that row for some reason. That would need a different formula:

Book1
	A	B	C	D	E
1	Contractor	Cost		Contractor	Total
2	A	1		A	2
3	B	3		B	3
4	B	1		C	2
5	B	4
6	B
7	C	5
8	C
9	A	4
10	C	1
Sheet1

 

[broncos347]

Thanks works great.