Excel CountIf Etc

marka87uk

I tried to this at work today and couldn't work out the correct formula so I ended up doing it by hand!

Here's the query...

Two columns; Amount and Reason(s):

Am | Re

---

20 | A
65 | BD
30 | C
55 | ABC
85 | DE
90 | AC

Now, I need to firstly count the number of entries for each reason letter. I tried, if I remember correctly, doing something like this: COUNTIF(B2:B7,"A"). It works for the entries with only one reason (in the example above it would count '1'), but for entries with multiple reasons it doesn't (it should count '3'). I tried the formula along with FIND() but with no luck. How can I do this?

Secondly I need to add up the total amount for each reason. Again, it would need to be able to include multiple reasons; in the example above the total amount for A would be (20+55+90), and B would be (65+55). How could I do this?

Thanks in advance, my head goes mushy after a while!

theCapt

got the 1st bit for you mate try this code and it should work:

=COUNTIF(B2:B7,"A*")

marka87uk

Thanks, I didn't realise wildcards were supported. A quick search has shown me that =COUNTIF(B2:B7,"*B*") will do what I need as it should ignore anything before or after the 'B' etc.

Now just to solve the second problem!

theCapt

the second problem is a bit more tricky than than first.

marka87uk

theCapt wrote:

the second problem is a bit more tricky than than first.

Yup I kinda realised that ... creating extra columns for A, B, C etc, then copying the amount value to the cell if the reason column contained the reason code, and adding up the total for the column, would work I think, but there must be a simpler way.

theCapt

yep it is called SQL, but then it will be a database rather than a spreadsheet.

infact it would be easier to do, using access than excel (using a database rather than a spreadsheet).

dmac_74

You will need to have your table as follows

Re | Am

---

A | 20
BD | 65
C |30
ABC |55
DE |85
AC |90

and then use =SUMIF(A2:B7,"*A*",B2:B7) and this gives 165

Fairwinds

Why would you need to transpose the data :

=SUMIF(B2:B7,"*a*",A2: A7)

does it