Using COUNTIF across multiple worksheets to cross-reference age & location

[Lemmiwinks]

Hi everyone

With previous help from this forum I've almost got my Excel sheet where I want it. However, I've still got one last hurdle to overcome and it is outside of my realms of Excel knowledge!

I have attached a dummy version of my spreadsheet to show what I am doing.

I would like to count how many people from each specified age range come from specific 'wards'. This would be a combined total across all active worksheets (as listed in Wards Info sheet, column A). Example, there were 4 people aged 35-44 from Primrose ward. Is it possible for Excel to do this kind of dual lookup and count? This info would be collated on 'Wards Info' sheet next to the overall ward count data already collated.

All help will be truly appreciated as always.

Many thanks in advance

[Saqib Qureshi]

Your Workbook doesnt seem to be in order. Can you be more specific.

[Lemmiwinks]

Hi

I'm unsure what you mean by being in order? There is only presently one worksheet 'Traditional Calligraphy' that has any specific data. I will populate the other sheets as and when the data becomes known.

Using the data in 'Traditional Calligraphy' as a guide, I can see there are two people who are aged in the 65+ age bracket and from 'West Park' ward. What I am looking to achieve within the sheet 'Ward Info' in columns H-O is that tally looking at the age bracket and ward across all sheets as listed in 'Ward Info' Column A.

Hope this helps.

I have tweaked the spreadsheet I had originally uploaded, so please use the version attached to this post please.

Cheers for your help.

[FlameRetired]

Using the data in 'Traditional Calligraphy' as a guide,

I find no 'Traditional Calligraphy' in the latest upload.

What I am looking to achieve within the sheet 'Ward Info' in columns H-O is that tally looking at the age bracket and ward across all sheets as listed in 'Ward Info' Column A.

I also find no sheet 'Ward Info'

Can you help us out?

[Lemmiwinks]

Hi

It appears I'm having an absolute nightmare. I uploaded the wrong file. So sorry. I shall take myself outside and give myself a good shoeing.

The correct file is attached to this post.

Cheers.

[FlameRetired]

I am getting lost and confused.

The instructions are not clear at all (at least not to me). I can not reconcile the instructions with the data and there is data (scant) in only some of the 18 sheets. The rest are empty. We need data to confirm our formulas are working properly.

Text "counts" embedded in text strings:

Example, there were 4 people aged 35-44 from Primrose ward.

• Please indicate how you arrived at this figure ... and please cite specific sheet(s) and cell(s).
• I find only one reference to Primrose ward (with an age range 65+) that has a 4 embedded in text () in 'Traditional Calligraphy' J12:M12. I would strongly recommend replacing those embedded "counts" with actual numbers only.

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SUBSTITUTE($A$1:$A$14,"'","'")&"'!$M:$M"),SUBSTITUTE(B1,"&","and")))

This suggests complications in the source and reference data (not accounted for yet?). This would best be taken care of with aggressive source and reference data cleanup rather than trying to reconcile the variations in formula.

This info would be collated on 'Wards Info' sheet next to the overall ward count data already collated.

This is not clear. There are two columns of counts.

Until these things are reconciled (and there may be more) I don't see anything I can work with here.

Sorry

[Lemmiwinks]

Hi FlameRetired

The data that is being collected is ongoing hence the initial scant data. In the latest upload attached to this post, there is data in two more worksheets. Hopefully, this will help.

Text "counts" embedded in text strings:
Example, there were 4 people aged 35-44 from Primrose ward.
Please indicate how you arrived at this figure ... and please cite specific sheet(s) and cell(s).

Sorry for the confusion here. I was merely giving an example as a guide. This was not based on specific data but just a method of trying to clarify what I was looking to calculate.

To be more explicit, on the 'Traditional Calligraphy' sheet, a good example of what I am trying to achieve would be:

2 people from 'West Park' ward in the age bracket '65+' - adding this total to the 'Ward Info' sheet as a running tally across all 14 worksheets as specified in Column A in the 'Ward Info' sheet.

The 'substitute' aspect is just to catch anything with 'and' or '&'. This was advised by another member on this forum.

As regards two columns of counts in the /Ward Info' worksheet, Column B and Column E are essentially the same count. I have split the wards in to two columns to represent two different geographical locations - Column B are wards in one area, Column E are wards in another area. To simplify the process, just focus on Column B wards if that helps. It is also the Column B wards that have been duplicated on the 'Ward Info'sheet with the age brackets above them. This is where I would like to collect the data from the previously specified worksheets (Column A in 'Ward Info') to show how many people from each ward in the different age brackets as a running total.

Hopefully this helps to clarify the requirements and provides enough data to create a working formula.

Many thanks and best wishes.

[FlameRetired]

If I understand correctly you want output in 'Ward Info' I2:O19.

I took the liberty of changing the whole column references to 1000 rows. Increase that as needed. Normally the IF(s) family of functions permits whole column references with little noticeable affect on performance. Since you are wrapping SUMPRODUCT around that everything changes. SUMPRODUCT has to calculate over one million rows ... across multiple sheets. It was really slow before changing the ranges.

Please find in the attached.

Please Login or Register  to view this content.

[Lemmiwinks]

Hi FlameRetired

You are indeed a star! That is exactly what I wanted to achieve.

Thank you so much for your patience and support. It is very much appreciated.

All the best

[FlameRetired]

You are welcome. Glad to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.