sum non adjacent cells with multiple count criteria

Hi,

My question relates to the thread below:
http://www.excelforum.com/excel-work...ent-cells.html

Could someone tell me how I could construct a single formula which counts non-adjacent cells and has more than one count criterion. I've tried doing this by summing multiple SUMPRODUCTs, but the formula becomes way too long.

Thanks

OK,

I've included a simple example.

Basically, without reorganising the data, I want to count the number of girls who are in team A or B. So I want to count the cells in the team column for the letters A or B but getting excel to add to the count only if the letters correspond to a girl member.

Sorry, before I just wanted to count every other (even) cell, but I've made the cells which need counting random in this example.

Many thanks!

Hi,

Thanks, but I wanted this calculation to be embedded as the logic in an IF calculation, so I would prefer not to use pivot tables. I was looking for something a little neater and won't appear on the spreadsheet, like:

=COUNTIF(in range: A1,A3,A5:A6 with criteria "A" or "B")

(sorry about the abuse of formula, but I thought it would be easier for you to understand what I wanted).

Sorry, I've probably confused things.
The gender thing was only an example I was using. What I meant was if I wanted the count range to be specific cells, say cells (B2:B5,B7:B8), with some nonadjacent.
So with the gender example in the attachment, I would want the count range to be (B4,B6:B7,B9), since these are the cells which correspond to girls. I.e, I want to select the count range manually rather than finding a way to make excel recognise the cells corresponding to girls automatically by adding an extra column.

Hope that has made it clearer.
I really appreciate your help.

I have to be able to randomly choose the cells I want to include in the count range. eg if I only wanted to include a specific 5 out of 10 girls in the count rather than all 10, the method you provided won't work in this case since it will count all the girls.

What I meant in the previous post is I want to forget column A (the name column) completely.
Let's say I have only one column in my table with the letters A-F of the alphabet placed randomly in each cell of that column and I want to count the number of cells in certain nonadjacent parts of that column containing either A or B. How would I do that?

Thanks.

Thank You!

It seems that the below formula works well for a criteria such as "A" or "B"...

=SUM(COUNTIF(INDIRECT({"B1:B2","B5","B8:B23"}),"A"),COUNTIF(INDIRECT({"B1:B2","B5","B8:B23"}),"B"))

But, it dose not work for a numeric criteria e.g. 3

I would be grateful if you give me your guidance ASAP.

The problem was solved...

It seems the mentioned formula doesn't work without SUM...
I mean for example you have to use SUM like below if you want to have just ONE criteria:
SUM(COUNTIF(INDIRECT(.....),....) , 0)