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:AG11>0),--(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 AG>0 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
|
Similar Threads
