Calculating ages and volumes: Macro or formula?
 

I have a preset spreadsheet that is supposed to calculate ages and
premiums. My plan was to use a macro to pull in a weekly demographic
"sheet 1" and from there the "worksheet" would calculate how many
people in a given age group and their volumes. For example on the
"worksheet" to calculate how many people are between the ages of 0 and
29 I have the formula:
=SUMPRODUCT(--(Sheet1!AU1:AU11=0),--(Sheet1!AU1:AU11<30))

However, That will get all the people between 0-29, and no necessarily
just those people who have a product. So what I need to do is figure
out the number of people between 0-29 (column AU) who have coverage
(column AG0 and column AH =0) So I figure I need to add in a =COUNTIF
but can I combine a countif with a sumproduct in one formula?

Then I was wondering if it would just be easier on "sheet 1" to create
a macro that would just do a loop through the sheet and calculate ages
and volumes. Even though I have AU1:AU11 on my formula that is just a
tester and I will have to figure out how to do it for a whole
spreadsheet that could run up to 10000 records.

Any thoughts on how I should go about this would be much appreciated.

Thanks!
Mary

Calculating ages and volumes: Macro or formula?
 

Sumproduct is not limited in the number of array arguments it accepts
(well, there might be one, but it's probably not relevant in practice).
You can enhance your formula as follows:

=SUMPRODUCT(--(Sheet1!AU1:AU11=0),--(Sheet1!AU1:AU11<30),--(Sheet1!
AG1:AG110),--(Sheet1!AH1:AH11=0))

Regards,
Steve

Dagonini wrote:

I have a preset spreadsheet that is supposed to calculate ages and
premiums. My plan was to use a macro to pull in a weekly demographic
"sheet 1" and from there the "worksheet" would calculate how many
people in a given age group and their volumes. For example on the
"worksheet" to calculate how many people are between the ages of 0 and
29 I have the formula:
=SUMPRODUCT(--(Sheet1!AU1:AU11=0),--(Sheet1!AU1:AU11<30))

However, That will get all the people between 0-29, and no necessarily
just those people who have a product. So what I need to do is figure
out the number of people between 0-29 (column AU) who have coverage
(column AG0 and column AH =0) So I figure I need to add in a =COUNTIF
but can I combine a countif with a sumproduct in one formula?

Then I was wondering if it would just be easier on "sheet 1" to create
a macro that would just do a loop through the sheet and calculate ages
and volumes. Even though I have AU1:AU11 on my formula that is just a
tester and I will have to figure out how to do it for a whole
spreadsheet that could run up to 10000 records.

Any thoughts on how I should go about this would be much appreciated.

Thanks!
Mary

Calculating ages and volumes: Macro or formula?
 

=SUMPRODUCT(--(Sheet1!AU1:AU11=0),--(Sheet1!AU1:AU11<30),--(AG1:AG110),--(AH1:AH11=0))

--
Regards,
Tom Ogilvy



"Dagonini" wrote:

I have a preset spreadsheet that is supposed to calculate ages and
premiums. My plan was to use a macro to pull in a weekly demographic
"sheet 1" and from there the "worksheet" would calculate how many
people in a given age group and their volumes. For example on the
"worksheet" to calculate how many people are between the ages of 0 and
29 I have the formula:
=SUMPRODUCT(--(Sheet1!AU1:AU11=0),--(Sheet1!AU1:AU11<30))

However, That will get all the people between 0-29, and no necessarily
just those people who have a product. So what I need to do is figure
out the number of people between 0-29 (column AU) who have coverage
(column AG0 and column AH =0) So I figure I need to add in a =COUNTIF
but can I combine a countif with a sumproduct in one formula?

Then I was wondering if it would just be easier on "sheet 1" to create
a macro that would just do a loop through the sheet and calculate ages
and volumes. Even though I have AU1:AU11 on my formula that is just a
tester and I will have to figure out how to do it for a whole
spreadsheet that could run up to 10000 records.

Any thoughts on how I should go about this would be much appreciated.

Thanks!
Mary