Hello,

I have two columns that I would like to divide across and
sum the quotients. If the right column is not a number or is
0 or blank, I don't want to include it in the sum. Example:

[code]
A B C
1 Room Area Area/Person
2 Break 144.10 100
3 Office 157.70 50
4 Hall
5 Toilet
6 Stacks 785.00 100
.....
Total Occupants: ????

I would like to write a formula that would work with the
array and still work if I add rows or delete values. Some rooms
are not applicable to the count but need to be included anyway.

Tried the array formula:
{=sum(if(and(isnumber(C1:C6),C1:C60)),B1:B6/C1:C6))}

but it did not work. Sumproduct would work if I was trying
to multiply. Anyone have any ideas? Thanks.

insertnamedefine
name it rngC
in the refers to box
=offset($c$1,0,0,counta($c:$c),1)
{=sum(if(and(isnumber(rngC),rngC0)),rngB/rngC))}

--
Don Guillett
SalesAid Software

"Doug Broad" wrote in message
...
Hello,

I have two columns that I would like to divide across and
sum the quotients. If the right column is not a number or is
0 or blank, I don't want to include it in the sum. Example:

[code]
A B C
1 Room Area Area/Person
2 Break 144.10 100
3 Office 157.70 50
4 Hall
5 Toilet
6 Stacks 785.00 100
....
Total Occupants: ????

I would like to write a formula that would work with the
array and still work if I add rows or delete values. Some rooms
are not applicable to the count but need to be included anyway.

Tried the array formula:
{=sum(if(and(isnumber(C1:C6),C1:C60)),B1:B6/C1:C6))}

but it did not work. Sumproduct would work if I was trying
to multiply. Anyone have any ideas? Thanks.

Thanks Don,
I could not get that to work. I did get it to work
if I eliminated the compound condition. IOW
{=sum(if(C2:C60,B2:B6/C2:C6))} worked
but adding a compound condition with the
"and" function, would not work. Perhaps that
is a limitation of the array formula.

Thanks again for your help.

Regards,
Doug Broad


"Don Guillett" wrote in message ...
insertnamedefine
name it rngC
in the refers to box
=offset($c$1,0,0,counta($c:$c),1)
{=sum(if(and(isnumber(rngC),rngC0)),rngB/rngC))}

--
Don Guillett
SalesAid Software

"Doug Broad" wrote in message
...
Hello,

I have two columns that I would like to divide across and
sum the quotients. If the right column is not a number or is
0 or blank, I don't want to include it in the sum. Example:

[code]
A B C
1 Room Area Area/Person
2 Break 144.10 100
3 Office 157.70 50
4 Hall
5 Toilet
6 Stacks 785.00 100
....
Total Occupants: ????

I would like to write a formula that would work with the
array and still work if I add rows or delete values. Some rooms
are not applicable to the count but need to be included anyway.

Tried the array formula:
{=sum(if(and(isnumber(C1:C6),C1:C60)),B1:B6/C1:C6))}

but it did not work. Sumproduct would work if I was trying
to multiply. Anyone have any ideas? Thanks.

"and" function, would not work. Perhaps that
is a limitation of the array formula.
The "and" function should work just fine. What I sent was a way to name your
ranges so they would be dynamic and then you could use them in the NON array
sumproduct function.

If you want to send me a SMALL workbook outlining your problem and giving
FULL DETAILS, I will take a look.

--
Don Guillett
SalesAid Software

"Doug Broad" wrote in message
...
Thanks Don,
I could not get that to work. I did get it to work
if I eliminated the compound condition. IOW
{=sum(if(C2:C60,B2:B6/C2:C6))} worked
but adding a compound condition with the
"and" function, would not work. Perhaps that
is a limitation of the array formula.

Thanks again for your help.

Regards,
Doug Broad


"Don Guillett" wrote in message
...
insertnamedefine
name it rngC
in the refers to box
=offset($c$1,0,0,counta($c:$c),1)
{=sum(if(and(isnumber(rngC),rngC0)),rngB/rngC))}

--
Don Guillett
SalesAid Software

"Doug Broad" wrote in message
...
Hello,

I have two columns that I would like to divide across and
sum the quotients. If the right column is not a number or is
0 or blank, I don't want to include it in the sum. Example:

[code]
A B C
1 Room Area Area/Person
2 Break 144.10 100
3 Office 157.70 50
4 Hall
5 Toilet
6 Stacks 785.00 100
....
Total Occupants: ????

I would like to write a formula that would work with the
array and still work if I add rows or delete values. Some rooms
are not applicable to the count but need to be included anyway.

Tried the array formula:
{=sum(if(and(isnumber(C1:C6),C1:C60)),B1:B6/C1:C6))}

but it did not work. Sumproduct would work if I was trying
to multiply. Anyone have any ideas? Thanks.

Thanks for the offer Don.
In this case, the workbook is 440K, with hundreds of macros
in it. I could cut and paste the relevant part of it but I described
it pretty well other than the exact location of the table.

It's part of a building code summary worksheet that is required
in North Carolina. The columns were right, but the rows are
169-??? depending on how many rooms there are in the project.
There is data above and below the table to be calculated. Perhaps
the counta function arguments should be refined?

In your illustration, would rngB need to be dynamically defined
as well as rngC? I tried defining it as a regular range and it didn't work.
It didn't occur to me that you were bypassing the need for an array
formula since you included the braces in your example. But with
my guess at how rngB was defined, it didn't work.

Thanks again.

Regards,
Doug

"Don Guillett" wrote in message ...
"and" function, would not work. Perhaps that
is a limitation of the array formula.
The "and" function should work just fine. What I sent was a way to name your
ranges so they would be dynamic and then you could use them in the NON array
sumproduct function.

If you want to send me a SMALL workbook outlining your problem and giving
FULL DETAILS, I will take a look.

--
Don Guillett
SalesAid Software

"Doug Broad" wrote in message
...
Thanks Don,
I could not get that to work. I did get it to work
if I eliminated the compound condition. IOW
{=sum(if(C2:C60,B2:B6/C2:C6))} worked
but adding a compound condition with the
"and" function, would not work. Perhaps that
is a limitation of the array formula.

Thanks again for your help.

Regards,
Doug Broad


"Don Guillett" wrote in message
...
insertnamedefine
name it rngC
in the refers to box
=offset($c$1,0,0,counta($c:$c),1)
{=sum(if(and(isnumber(rngC),rngC0)),rngB/rngC))}

--
Don Guillett
SalesAid Software

"Doug Broad" wrote in message
...
Hello,

I have two columns that I would like to divide across and
sum the quotients. If the right column is not a number or is
0 or blank, I don't want to include it in the sum. Example:

[code]
A B C
1 Room Area Area/Person
2 Break 144.10 100
3 Office 157.70 50
4 Hall
5 Toilet
6 Stacks 785.00 100
....
Total Occupants: ????

I would like to write a formula that would work with the
array and still work if I add rows or delete values. Some rooms
are not applicable to the count but need to be included anyway.

Tried the array formula:
{=sum(if(and(isnumber(C1:C6),C1:C60)),B1:B6/C1:C6))}

but it did not work. Sumproduct would work if I was trying
to multiply. Anyone have any ideas? Thanks.

=SUM((ISNUMBER(C1:C6))*(C1:C60)*IF(ISNUMBER(B1:B6/C1:C6),B1:B6/C1:C6))

Array entered will work

--
Regards,
Tom Ogilvy





"Doug Broad" wrote in message
...
Thanks for the offer Don.
In this case, the workbook is 440K, with hundreds of macros
in it. I could cut and paste the relevant part of it but I described
it pretty well other than the exact location of the table.

It's part of a building code summary worksheet that is required
in North Carolina. The columns were right, but the rows are
169-??? depending on how many rooms there are in the project.
There is data above and below the table to be calculated. Perhaps
the counta function arguments should be refined?

In your illustration, would rngB need to be dynamically defined
as well as rngC? I tried defining it as a regular range and it didn't
work.
It didn't occur to me that you were bypassing the need for an array
formula since you included the braces in your example. But with
my guess at how rngB was defined, it didn't work.

Thanks again.

Regards,
Doug

"Don Guillett" wrote in message
...
"and" function, would not work. Perhaps that
is a limitation of the array formula.
The "and" function should work just fine. What I sent was a way to name
your
ranges so they would be dynamic and then you could use them in the NON
array
sumproduct function.

If you want to send me a SMALL workbook outlining your problem and
giving
FULL DETAILS, I will take a look.

--
Don Guillett
SalesAid Software

"Doug Broad" wrote in message
...
Thanks Don,
I could not get that to work. I did get it to work
if I eliminated the compound condition. IOW
{=sum(if(C2:C60,B2:B6/C2:C6))} worked
but adding a compound condition with the
"and" function, would not work. Perhaps that
is a limitation of the array formula.

Thanks again for your help.

Regards,
Doug Broad


"Don Guillett" wrote in message
...
insertnamedefine
name it rngC
in the refers to box
=offset($c$1,0,0,counta($c:$c),1)
{=sum(if(and(isnumber(rngC),rngC0)),rngB/rngC))}

--
Don Guillett
SalesAid Software

"Doug Broad" wrote in message
...
Hello,

I have two columns that I would like to divide across and
sum the quotients. If the right column is not a number or is
0 or blank, I don't want to include it in the sum. Example:

[code]
A B C
1 Room Area Area/Person
2 Break 144.10 100
3 Office 157.70 50
4 Hall
5 Toilet
6 Stacks 785.00 100
....
Total Occupants: ????

I would like to write a formula that would work with the
array and still work if I add rows or delete values. Some rooms
are not applicable to the count but need to be included anyway.

Tried the array formula:
{=sum(if(and(isnumber(C1:C6),C1:C60)),B1:B6/C1:C6))}

but it did not work. Sumproduct would work if I was trying
to multiply. Anyone have any ideas? Thanks.