Help to convert COUNTIFS to VBA

[JohnBausz]

I have used a combination of COUNTIF & COUNTIFS formulas to calculate the age of members from 7 sites
While it works it is slow to execute and cumbersome to manage.
Sample code

    Range("D10").FormulaR1C1 = "=COUNTIFS(SITE,""Botany"",AGE,""<=18"")"
    Range("E10").FormulaR1C1 = "=COUNTIFS(SITE,""Botany"",AGE,"">=19"",AGE,""<=20"")"
    Range("F10").FormulaR1C1 = "=COUNTIFS(SITE,""Botany"",AGE,"">=21"",AGE,""<=25"")"
    Range("G10").FormulaR1C1 = "=COUNTIFS(SITE,""Botany"",AGE,"">=26"",AGE,""<=30"")"
    Range("H10").FormulaR1C1 = "=COUNTIFS(SITE,""Botany"",AGE,"">=31"",AGE,""<=35"")"
    Range("I10").FormulaR1C1 = "=COUNTIFS(SITE,""Botany"",AGE,"">=36"",AGE,""<=40"")"

The named ranges SITE & Botany are full column ranges. My first problem is that the sites may vary from time to time. Is there a way to have the formula look up a list to get the name for each site.

Is there a way to streamline the formulas in VBA. I'm keen to learn more about VBA

I have attached an example workbook.

Cheers

[Paul_Hossler]

Not sure you need to use VBA

I added a little Age Group table to take the age and put it into a helper column

The COUNTIFS formula can just use the row readers in B7:B19 and the column headers in D9:N9

One formula with B column fixed and row 6 fixed can be copied to the data block

=COUNTIFS(SITE,$B10,AGE,D$6)

Just a thought

Capture.JPG

[JohnBausz]

Damn......This is a cunning piece of work . Its going to take me a bit to get my head around it.
On the Data sheet you've used the labels to provide the criteria for Average Ages and Unit Average ages, pretty!
However on Sheet2 I'm a bit lost, not having used ISERROR, INDEX and MATCH before. Would you mind giving me a bit of an overview on the formula please.

Is there a reward system for great responses?

cheers.

[Aussiebear]

Have a look here http://www.contextures.com/xlFunctions03.html. Debra runs one of the best sites for excel education that I've seen.

[snb]

I was more thinking about a pivot table.

[JohnBausz]

Thanks for that. Had a quick look. Mind boggling it is

cheers

[Paul_Hossler]

However on Sheet2 I'm a bit lost, not having used ISERROR, INDEX and MATCH before. Would you mind giving me a bit of an overview on the formula please.

=IF(ISERROR(H5),"Age Not Recorded",INDEX($L$2:$M$12,MATCH(H5,$L:$L,1),2))

Sure, in pseudo-code

Using J5 with Age=34:

If H5 is an Error Then (ISERROR)

Put "Age Not Recorded" in J5

Else

Look for (MATCH) the row number of the first value in L:L that is >= (because of the '1' in the 3rd position) the Age. Row 5 (30 & 26-30)

Use that value (5) as row and 2 as column into the array L2:M12

Put "31-35" in J5

End If

[JohnBausz]

Hey, thanks for the explain and for the assistance. You've been a great help and given me another way to look at things excel

I'll mark this thread as solved.

Until my next plea for help,

cheers