Excel Question

LSainsbury · 08-01-2012, 19:59

Hi All,

Excel experts required!

I have a column in Excel which has the letter A or C. I want to count the number of A's and display as a total and the same for the C's.

Whats the formula for counting the A's and C's?

Thanks

gazzae · 08-01-2012, 20:01

Sumif

On phone so can't be more detailed.

LSainsbury · 08-01-2012, 20:25

=SUMIF(B5:B144, "A")

Returns 0 - which is incorrect...

---------- Post added at 21:25 ---------- Previous post was at 21:10 ----------

From Excel Help:

Quote:

The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.

So it won't count the number of A's or C's....great. How else can I do this??

progers · 08-01-2012, 20:31

=COUNTIF(A1:A28,"A") should do the trick

LSainsbury · 08-01-2012, 20:44

Quote:

Originally Posted by progers

=COUNTIF(A1:A28,"A") should do the trick

Ahh - brilliant. Worked a treat. Thanks!

cookie_365 · 09-01-2012, 17:28

Yes, SUMIF is where you add -rather than count- numbers together that match certain criteria.

gazzae · 09-01-2012, 19:13

Doh my fault for not reading the op properly

cookie_365 · 10-01-2012, 16:23

wasn't having a needle or anything like that.

I was just posting for any Excel newbies who might stumble on the thread in future with a similar question

LSainsbury · 04-03-2012, 17:03

Quote:

Originally Posted by progers

=COUNTIF(A1:A28,"A") should do the trick

Bump!

How do I do this for two criteria?

Say - column A1:A28, "A" and column B1: B28 contains "D" - I want to count this as 1. COUNTIF seems to add the up making 2!

qasdfdsaq · 04-03-2012, 17:14

Easiest way, make a third column C1:C28 that =1 if A[row]=A and B[row]=D.

cookie_365 · 04-03-2012, 21:19

If you can't add extra columns, then you can use the bizarre function SUMPRODUCT:

=SUMPRODUCT((A1:A28=A)*(B1:B28="D"))

Yes, I know, ignore the words 'SUM' and 'PRODUCT' in the function name

LSainsbury · 05-03-2012, 18:08

Quote:

Originally Posted by cookie_365

If you can't add extra columns, then you can use the bizarre function SUMPRODUCT:

=SUMPRODUCT((A1:A28=A)*(B1:B28="D"))

Yes, I know, ignore the words 'SUM' and 'PRODUCT' in the function name

Brilliant - exactly what I needed! Who would have thought wedding guest list planning was this exciting! This is probably the most complex thing my copy of Excel has had to do - ever!

colin25 · 05-03-2012, 18:21

Quote:

Originally Posted by LSainsbury

Brilliant - exactly what I needed! Who would have thought wedding guest list planning was this exciting! This is probably the most complex thing my copy of Excel has had to do - ever!

lol..mind boggles

cookie_365 · 06-03-2012, 11:49

Quote:

Originally Posted by LSainsbury

Brilliant - exactly what I needed! Who would have thought wedding guest list planning was this exciting! This is probably the most complex thing my copy of Excel has had to do - ever!

You're welcome