Count of cells referencing 2 columns but with varying numbers

[bonsaitrees]

Hi,

I am analysing data with male and female participants. Their gender data is in B2 down, classed as F (female) or M (male). The group can have up to 40 subjects in it but is variable in nature. In W2 down is their respective performance targets. I want to count

All females with a performance target of between -0.76 or less, 0 to -0.75 and those above 0.
All males with a performance target of between -0.76 or less, 0 to -0.75 and those above 0.

As the group can shrink and grow and there is data below this that could influence the accuracy of the count it needs to be dynamic in nature. There is always a space separating this and the next dataset below it.

Any help much appreciated.

[FDibbins]

I think it would help if you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[Glenn Kennedy]

You can use variants of the COUNTIFS formula, as shown in the attached file.

If this isn't it, please attach a sample sheet, as requested above.

[FDibbins]

Glenn, did you see this part?

As the group can shrink and grow and there is data below this that could influence the accuracy of the count it needs to be dynamic in nature. There is always a space separating this and the next dataset below it.

I almost missed it too

[Glenn Kennedy]

No. Well, sort-of no. I (mis) read it as meaning that there were gaps between rows. However, now I see what the OP means... I think. it would be much clearer with a sample sheet.


So, Bonsaitrees... Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[bonsaitrees]

File attached.

Expected outcome on 2nd tab. Yellow coloured columns - annotated underneath with text boxes. Hope this is OK, Glenn.

[Glenn Kennedy]

I just saw your attachment. Can you confirm that this one that I'd been playing with does what you want??

Edit: I attached the wrong file!!

[FDibbins]

In the sample file from post 6, do you have more tables below that, with more M/F references? If not, the suggestion from Glenn should work

[bonsaitrees]

In the sample file from post 6, do you have more tables below that, with more M/F references? If not, the suggestion from Glenn should work

No more tables below that.

@Glenn - no worries mate. I'll watch for the correct upload.

[FDibbins]

Is there anything to identify 1 table from the next? (and I dont mean empty rows)

[Glenn Kennedy]

The correct sheet is there now (Post 7). Is that what you had hoped to see??

[bonsaitrees]

I'm confused by your file. In my original workbook there were 2 sheets, sheet 1 and one next to it called SL. In SL I had done a manual count of the expected outcome I had expected to see in a table format. Your sheet has one column of M and F's from B2 to B40 in column B with header in B1. No formulae are present. This is why I presumed you had uploaded the wrong file.

[Glenn Kennedy]

I was working on a sheet that I mocked up myself. I had completed working on it when you posted your sheet. rather than start over and work on your sheet, I posted mine to see if it was doing what you expected, based on your description.

So, open the file at post 7 and see if it is doing what you want. the formulae are in Z2 to AC4.

[bonsaitrees]

Ah thanks for pointing me at the formulae. In the W column in sheet 1 I can't see any value over 0 and your formula returns several. Has the minus been omitted from the formulae? The values were:

-0.76 or less, 0 to -0.75 and those above 0.

[Glenn Kennedy]

Rows 2, 3, 7, 10, 11, 19, 24, etc... all are greater than zero.

[bonsaitrees]

In my sample sheet row 2 is -1.97, row 3 is -0.18. Your sheet also has 40 rows of data. In mine it is row 2 to row 24. In your sheet row 2 it is 0.08 - I've done a search and the value 0.08 is not present in my sheet at all. Have you copy and pasted the right data?

[Glenn Kennedy]

How many times... For now FORGET ABOUT YOUR SHEET.

I have not worked on your sheet. I do not want to spend time transferring my formula to your sheet until I know that it is doing what you want.

So look at my sheet, post 7, complete with formulae, complete with values greater than zero and tell me if it is doing what you need. Once you agree that it is what you want THEN and ONLY THEN will I set it up on your sheet (which you could have supplied right from the get-go). Had you done so, none of this to-ing and fro-ing would have been necessary.

[bonsaitrees]

Sorry mate. Yes these values are correct. Thank you for your help.

[Glenn Kennedy]

Il5set this up on your sheet in the morning, then...

[Glenn Kennedy]

Here you go. Check it over carefully!!

[bonsaitrees]

Thank you so much and apologies for my stupidity earlier!

[Glenn Kennedy]

You're welcome...