Count if formula and subtotals

M

Mark

Hi

Have two columns of data, for example:

Dept Personnel
1 60
2 78
3 35
3 45
2 23
3 24
4 67
2 23
1 23
2 34
3 34

I have already placed the formula =count if(A1:a1000, "1") and so on
at the base of column A to count occurences of departments. At the
base of column B I wish to have a calculation that totals the amounts
of column b (personnel) that correspond with each dept (i.e. something
to the extent of if looking at number of personnel corresponding to
department 3, the result of the formula should be 138!

Any help appreciated!
 
A

Andy B

Hi

One way is to use SUMIF(). There are many others, but as you are already
using COUNTIF, you might as well use SUMIF!!
 
D

Dave Peterson

You may want to look at Data|pivottable, too.

You can get a lot of nice summaries with just a little work.

If you want to learn more about pivottables, here are a few links.

Debra Dalgleish's pictures at Jon Peltier's site:
http://www.geocities.com/jonpeltier/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx
 
M

Mark

Thanks Andy

Without being a pain, how can i make the sum if formula work so that
in respect of the data below, i can total the amounts of column b
(personnel) that correspond with specific departments in column a

i.e. =sumif(a1:a11,"1") will give me all occurences of 1 in that
column, but i want the total personnel in the second column (b) to
count the personnel against all the coccurences of "1" to give me a
total for that single department and so on.


Any suggested formulas based on the data below?
Mark
 
M

Mark

Thankyou Alf. Could not see the wood for the trees!!

Interestingly enough, when I have to calculate totals against
occurences of department code 1*.

When i use the equation

=sumif(A1:A11,"1",B1:B11)+sumif(A1:A11,"1*",B1:B11), for example, the
totals are incorrect. However when I use 1.0* instead of 1* they
correct themselves.

Any ideas why?? 2*, 3* and so on seem to work okay, but not 1*
 
A

AlfD

Hi!

What are the dept codes? Text? Integer? Real number?
How does the * come into it? What if anything is being hidden b
formatting?

Al
 
M

Mark

Hi Alf

Department codes are simply Dept 1, Dept 2, Dept 1*, Depart 2* etc

However when I wish to add the data against department 1 with
department 1*, the calculation

=sumif(A1:A11,"1",B1:B11)+sumif(A1:A11,"1*",B1:B11)is incorrect.
However when I use 1.0* to represent dept 1*, it works

Any ideas why?? The following sum works however

=sumif(A1:A11,"2",B1:B11)+sumif(A1:A11,"2*",B1:B11)

2*, 3* and so on seem to work okay, but not 1* (have to use 1.0*)

Dept Personnel
1 60
2 78
3 35
3 45
2* 23
3 24
4 67
2 23
1* 23
2 34
3 34
1* 70


Nothing is being hidden by formatting as such!

Thanks
Mark
 
A

AlfD

Hi!

I copied your column of data from the post and pasted it onto
worksheet.
Text to columns put the columns back into shape.

The formula works perfectly well on 1* and 2* (which are formatted a
text) and on the rest of the numbers (which are formatted as numbers).

If I change one of the 1*'s to 1.0*, the formula still gives the sam
answer. Surprised me.
If I now change the 1* in the formula to 1.0*, it does not recognis
the 1* (nor would I expect it to).

Put 1.0* in both col A and the formula: no problem.

So what is awry? Change one of the 1*'s on col A to 11*. Still picke
up by the formula with "1*".

"1*" in the formula is behaving as 1 with a wildcard *.

I recommend you get rid of * in your codes.

Al
 
A

AlfD

P.S.

I've now done what I (we) should have done earlier.
Excel Help tells us clearly that * and ? can be used as wildcar
characters in a "Find"..

Good old Excel Help.

Al
 
Top