PDA

View Full Version : [SOLVED] Help to convert COUNTIFS to VBA



JohnBausz
03-31-2016, 03:08 PM
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
03-31-2016, 04:57 PM
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

15785

JohnBausz
03-31-2016, 05:57 PM
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
03-31-2016, 07:27 PM
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
04-01-2016, 02:07 AM
I was more thinking about a pivot table.

JohnBausz
04-01-2016, 05:30 AM
Thanks for that. Had a quick look. Mind boggling it is :)

cheers

Paul_Hossler
04-01-2016, 05:33 AM
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
04-02-2016, 03:18 PM
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