Thanks to Biff for the last response.

One more for the group: Can I reference the "=", "<",
or "" etc sign in a SUMPRODUCT function of the following
form:

"=SUMPRODUCT((Range1=Criteria1)*(Range2Criteria2) *
(Range3<Criteria3)"

I can use an indirect to reference the range, and direct
references to the criteria. In countif and sumif functions
I can direct reference "=", "<", or "" etc signs but
can't seem to get it right for this sumproduct function.

If you have a way, please check out the min, max and mode
formulae below for the same question.

Thanks
BobT


Hi!

All are array formulas, enter with the key combo of
CTRL,SHIFT,ENTER:

=MODE(IF(A1:A10="x",IF(B1:B10="y",IF(C1:C10="z",D1 :D10))))

=MIN(IF(A1:A10="x",IF(B1:B10="y",IF(C1:C10="z",D1: D10))))

=MAX(IF(A1:A10="x",IF(B1:B10="y",IF(C1:C10="z",D1: D10))))

Biff

-----Original Message-----

Thanks again for the previous response.
Is there a way to get the mode min and or max with
multiple criteria?



-----Original Message-----
You're close -- SUMPRODUCT does the job, but you've left
off the final piece:
"=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )*
(Range3=Criteria3)*Range4)"

"BobT" wrote in message
...

I want to get an average based on multiple criteria
without having to sort the records and identify the range.
Of course I can get to the average if I can get the sum.

I'm aware that I can sum records within a range that meet
a codition outside the range using the SUMIF function
"=SUMIF(Range,criteria,sum range)"

I can also count records that meet multiple criteria
using
the SUMPRODUCT function
"=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )*
(Range3=Criteria3)"

I've also seen this array formula to count records that
meet multiple criteria:
{=SUM(IF(Range1=Criteria1,IF(Range2=Criteria2,I F
(Range3=Criteria3,1,0)))
But the SUMPRODUCT formula is easier to work with.

However, I have not found or figured out a sum formula
for
multple criteria. Any help out there?
Thanks
BobT

That SUMPRODUCT formula already uses , < and =, so the answer is yes is it
not?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"BobT" wrote in message
...
Thanks to Biff for the last response.

One more for the group: Can I reference the "=", "<",
or "" etc sign in a SUMPRODUCT function of the following
form:

"=SUMPRODUCT((Range1=Criteria1)*(Range2Criteria2) *
(Range3<Criteria3)"

I can use an indirect to reference the range, and direct
references to the criteria. In countif and sumif functions
I can direct reference "=", "<", or "" etc signs but
can't seem to get it right for this sumproduct function.

If you have a way, please check out the min, max and mode
formulae below for the same question.

Thanks
BobT


Hi!

All are array formulas, enter with the key combo of
CTRL,SHIFT,ENTER:

=MODE(IF(A1:A10="x",IF(B1:B10="y",IF(C1:C10="z",D1 :D10))))

=MIN(IF(A1:A10="x",IF(B1:B10="y",IF(C1:C10="z",D1: D10))))

=MAX(IF(A1:A10="x",IF(B1:B10="y",IF(C1:C10="z",D1: D10))))

Biff

-----Original Message-----

Thanks again for the previous response.
Is there a way to get the mode min and or max with
multiple criteria?



-----Original Message-----
You're close -- SUMPRODUCT does the job, but you've left
off the final piece:
"=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )*
(Range3=Criteria3)*Range4)"

"BobT" wrote in message
...

I want to get an average based on multiple criteria
without having to sort the records and identify the range.
Of course I can get to the average if I can get the sum.

I'm aware that I can sum records within a range that meet
a codition outside the range using the SUMIF function
"=SUMIF(Range,criteria,sum range)"

I can also count records that meet multiple criteria
using
the SUMPRODUCT function
"=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )*
(Range3=Criteria3)"

I've also seen this array formula to count records that
meet multiple criteria:
{=SUM(IF(Range1=Criteria1,IF(Range2=Criteria2,I F
(Range3=Criteria3,1,0)))
But the SUMPRODUCT formula is easier to work with.

However, I have not found or figured out a sum formula
for
multple criteria. Any help out there?
Thanks
BobT

I'm trying to refence the range and criteria so that I can
change them easily to look at different columns of data
and different criteria. Consider the following where
ranges are defined in column A and criteria in Column B.

=SUMPRODUCT((INDIRECT(A1)=B1)*(INDIRECT(A2)=B2)*(I NDIRECT
(A3)=B3)*(INDIRECT(A4)=B4))

This works, but if I try to reference the = sign or a < or
sign in column C that doesn't work.

It will work in a sumif or countif function
=Sumif(indirect(A1),C1&B1) where ranges are defined in
column A, criteria in Column B, and =, <, , =<, =, or <
in column C.



-----Original Message-----
That SUMPRODUCT formula already uses , < and =, so the
answer is yes is it
not?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"BobT" wrote in message
...
Thanks to Biff for the last response.

One more for the group: Can I reference the "=", "<",
or "" etc sign in a SUMPRODUCT function of the
following
form:

"=SUMPRODUCT((Range1=Criteria1)*(Range2Criteria2) *
(Range3<Criteria3)"

I can use an indirect to reference the range, and direct
references to the criteria. In countif and sumif
functions
I can direct reference "=", "<", or "" etc signs but
can't seem to get it right for this sumproduct function.

If you have a way, please check out the min, max and
mode
formulae below for the same question.

Thanks
BobT


Hi!

All are array formulas, enter with the key combo of
CTRL,SHIFT,ENTER:

=MODE(IF(A1:A10="x",IF(B1:B10="y",IF
(C1:C10="z",D1:D10))))

=MIN(IF(A1:A10="x",IF(B1:B10="y",IF
(C1:C10="z",D1:D10))))

=MAX(IF(A1:A10="x",IF(B1:B10="y",IF
(C1:C10="z",D1:D10))))

Biff

-----Original Message-----

Thanks again for the previous response.
Is there a way to get the mode min and or max with
multiple criteria?



-----Original Message-----
You're close -- SUMPRODUCT does the job, but you've
left
off the final piece:
"=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )*
(Range3=Criteria3)*Range4)"

"BobT" wrote in message
...

I want to get an average based on multiple criteria
without having to sort the records and identify the
range.
Of course I can get to the average if I can get the
sum.

I'm aware that I can sum records within a range that
meet
a codition outside the range using the SUMIF function
"=SUMIF(Range,criteria,sum range)"

I can also count records that meet multiple criteria
using
the SUMPRODUCT function
"=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )*
(Range3=Criteria3)"

I've also seen this array formula to count records that
meet multiple criteria:
{=SUM(IF(Range1=Criteria1,IF(Range2=Criteria2,I F
(Range3=Criteria3,1,0)))
But the SUMPRODUCT formula is easier to work with.

However, I have not found or figured out a sum formula
for
multple criteria. Any help out there?
Thanks
BobT




.

This works fine for me

=SUMPRODUCT((INDIRECT(A1)=B1)*(INDIRECT(A2)=B2)*( INDIRECT(A3)=B3)*(INDIRECT
(A4)=B4))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"BobT" wrote in message
...
I'm trying to refence the range and criteria so that I can
change them easily to look at different columns of data
and different criteria. Consider the following where
ranges are defined in column A and criteria in Column B.

=SUMPRODUCT((INDIRECT(A1)=B1)*(INDIRECT(A2)=B2)*(I NDIRECT
(A3)=B3)*(INDIRECT(A4)=B4))

This works, but if I try to reference the = sign or a < or
sign in column C that doesn't work.

It will work in a sumif or countif function
=Sumif(indirect(A1),C1&B1) where ranges are defined in
column A, criteria in Column B, and =, <, , =<, =, or <
in column C.



-----Original Message-----
That SUMPRODUCT formula already uses , < and =, so the
answer is yes is it
not?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"BobT" wrote in message
...
Thanks to Biff for the last response.

One more for the group: Can I reference the "=", "<",
or "" etc sign in a SUMPRODUCT function of the
following
form:

"=SUMPRODUCT((Range1=Criteria1)*(Range2Criteria2) *
(Range3<Criteria3)"

I can use an indirect to reference the range, and direct
references to the criteria. In countif and sumif
functions
I can direct reference "=", "<", or "" etc signs but
can't seem to get it right for this sumproduct function.

If you have a way, please check out the min, max and
mode
formulae below for the same question.

Thanks
BobT


Hi!

All are array formulas, enter with the key combo of
CTRL,SHIFT,ENTER:

=MODE(IF(A1:A10="x",IF(B1:B10="y",IF
(C1:C10="z",D1:D10))))

=MIN(IF(A1:A10="x",IF(B1:B10="y",IF
(C1:C10="z",D1:D10))))

=MAX(IF(A1:A10="x",IF(B1:B10="y",IF
(C1:C10="z",D1:D10))))

Biff

-----Original Message-----

Thanks again for the previous response.
Is there a way to get the mode min and or max with
multiple criteria?



-----Original Message-----
You're close -- SUMPRODUCT does the job, but you've
left
off the final piece:
"=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )*
(Range3=Criteria3)*Range4)"

"BobT" wrote in message
...

I want to get an average based on multiple criteria
without having to sort the records and identify the
range.
Of course I can get to the average if I can get the
sum.

I'm aware that I can sum records within a range that
meet
a codition outside the range using the SUMIF function
"=SUMIF(Range,criteria,sum range)"

I can also count records that meet multiple criteria
using
the SUMPRODUCT function
"=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )*
(Range3=Criteria3)"

I've also seen this array formula to count records that
meet multiple criteria:
{=SUM(IF(Range1=Criteria1,IF(Range2=Criteria2,I F
(Range3=Criteria3,1,0)))
But the SUMPRODUCT formula is easier to work with.

However, I have not found or figured out a sum formula
for
multple criteria. Any help out there?
Thanks
BobT




.

Right
I can reference the range and the criteria, but not the =,
<, etc condition. I want to be able to reference the
condition also. I'm trying to set up a cross tab for
survey data that is flexible so it can adapted to
different surveys. I might want to show an age range in
one column, requiring = and <= conditions, but in another
column i might want females in the Northeast, requiring
two = conditions. I want to be able to change the =, <,
condtions at the top of the column and have the SUMPRODUCT
function reference that without going into the formula and
changing =, <, conditions to suit my specific needs.


-----Original Message-----
This works fine for me

=SUMPRODUCT((INDIRECT(A1)=B1)*(INDIRECT(A2)=B2)* (INDIRECT
(A3)=B3)*(INDIRECT
(A4)=B4))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"BobT" wrote in message
...
I'm trying to refence the range and criteria so that I
can
change them easily to look at different columns of data
and different criteria. Consider the following where
ranges are defined in column A and criteria in Column B.

=SUMPRODUCT((INDIRECT(A1)=B1)*(INDIRECT(A2)=B2)*
(INDIRECT
(A3)=B3)*(INDIRECT(A4)=B4))

This works, but if I try to reference the = sign or a <
or
sign in column C that doesn't work.

It will work in a sumif or countif function
=Sumif(indirect(A1),C1&B1) where ranges are defined in
column A, criteria in Column B, and =, <, , =<, =, or
<
in column C.



-----Original Message-----
That SUMPRODUCT formula already uses , < and =, so the
answer is yes is it
not?

--

HTH

RP
(remove nothere from the email address if mailing
direct)


"BobT" wrote in message
...
Thanks to Biff for the last response.

One more for the group: Can I reference the "=", "<",
or "" etc sign in a SUMPRODUCT function of the
following
form:

"=SUMPRODUCT((Range1=Criteria1)*(Range2Criteria2) *
(Range3<Criteria3)"

I can use an indirect to reference the range, and
direct
references to the criteria. In countif and sumif
functions
I can direct reference "=", "<", or "" etc signs but
can't seem to get it right for this sumproduct
function.

If you have a way, please check out the min, max and
mode
formulae below for the same question.

Thanks
BobT


Hi!

All are array formulas, enter with the key combo of
CTRL,SHIFT,ENTER:

=MODE(IF(A1:A10="x",IF(B1:B10="y",IF
(C1:C10="z",D1:D10))))

=MIN(IF(A1:A10="x",IF(B1:B10="y",IF
(C1:C10="z",D1:D10))))

=MAX(IF(A1:A10="x",IF(B1:B10="y",IF
(C1:C10="z",D1:D10))))

Biff

-----Original Message-----

Thanks again for the previous response.
Is there a way to get the mode min and or max with
multiple criteria?



-----Original Message-----
You're close -- SUMPRODUCT does the job, but you've
left
off the final piece:
"=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )*
(Range3=Criteria3)*Range4)"

"BobT" wrote in message
...

I want to get an average based on multiple criteria
without having to sort the records and identify the
range.
Of course I can get to the average if I can get the
sum.

I'm aware that I can sum records within a range that
meet
a codition outside the range using the SUMIF
function
"=SUMIF(Range,criteria,sum range)"

I can also count records that meet multiple criteria
using
the SUMPRODUCT function
"=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )*
(Range3=Criteria3)"

I've also seen this array formula to count records
that
meet multiple criteria:
{=SUM(IF(Range1=Criteria1,IF(Range2=Criteria2,I F
(Range3=Criteria3,1,0)))
But the SUMPRODUCT formula is easier to work with.

However, I have not found or figured out a sum
formula
for
multple criteria. Any help out there?
Thanks
BobT




.



.

You'd need to use a macro for that, maybe an input box. I personally avoid
using indirect because it is
volatile and IMHO it is overkill

--

Regards,

Peo Sjoblom


"BobT" wrote in message
...
Right
I can reference the range and the criteria, but not the =,
<, etc condition. I want to be able to reference the
condition also. I'm trying to set up a cross tab for
survey data that is flexible so it can adapted to
different surveys. I might want to show an age range in
one column, requiring = and <= conditions, but in another
column i might want females in the Northeast, requiring
two = conditions. I want to be able to change the =, <,
condtions at the top of the column and have the SUMPRODUCT
function reference that without going into the formula and
changing =, <, conditions to suit my specific needs.


-----Original Message-----
This works fine for me

=SUMPRODUCT((INDIRECT(A1)=B1)*(INDIRECT(A2)=B2)* (INDIRECT
(A3)=B3)*(INDIRECT
(A4)=B4))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"BobT" wrote in message
...
I'm trying to refence the range and criteria so that I
can
change them easily to look at different columns of data
and different criteria. Consider the following where
ranges are defined in column A and criteria in Column B.

=SUMPRODUCT((INDIRECT(A1)=B1)*(INDIRECT(A2)=B2)*
(INDIRECT
(A3)=B3)*(INDIRECT(A4)=B4))

This works, but if I try to reference the = sign or a <
or
sign in column C that doesn't work.

It will work in a sumif or countif function
=Sumif(indirect(A1),C1&B1) where ranges are defined in
column A, criteria in Column B, and =, <, , =<, =, or
<
in column C.



-----Original Message-----
That SUMPRODUCT formula already uses , < and =, so the
answer is yes is it
not?

--

HTH

RP
(remove nothere from the email address if mailing
direct)


"BobT" wrote in message
...
Thanks to Biff for the last response.

One more for the group: Can I reference the "=", "<",
or "" etc sign in a SUMPRODUCT function of the
following
form:

"=SUMPRODUCT((Range1=Criteria1)*(Range2Criteria2) *
(Range3<Criteria3)"

I can use an indirect to reference the range, and
direct
references to the criteria. In countif and sumif
functions
I can direct reference "=", "<", or "" etc signs but
can't seem to get it right for this sumproduct
function.

If you have a way, please check out the min, max and
mode
formulae below for the same question.

Thanks
BobT


Hi!

All are array formulas, enter with the key combo of
CTRL,SHIFT,ENTER:

=MODE(IF(A1:A10="x",IF(B1:B10="y",IF
(C1:C10="z",D1:D10))))

=MIN(IF(A1:A10="x",IF(B1:B10="y",IF
(C1:C10="z",D1:D10))))

=MAX(IF(A1:A10="x",IF(B1:B10="y",IF
(C1:C10="z",D1:D10))))

Biff

-----Original Message-----

Thanks again for the previous response.
Is there a way to get the mode min and or max with
multiple criteria?



-----Original Message-----
You're close -- SUMPRODUCT does the job, but you've
left
off the final piece:
"=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )*
(Range3=Criteria3)*Range4)"

"BobT" wrote in message
...

I want to get an average based on multiple criteria
without having to sort the records and identify the
range.
Of course I can get to the average if I can get the
sum.

I'm aware that I can sum records within a range that
meet
a codition outside the range using the SUMIF
function
"=SUMIF(Range,criteria,sum range)"

I can also count records that meet multiple criteria
using
the SUMPRODUCT function
"=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )*
(Range3=Criteria3)"

I've also seen this array formula to count records
that
meet multiple criteria:
{=SUM(IF(Range1=Criteria1,IF(Range2=Criteria2,I F
(Range3=Criteria3,1,0)))
But the SUMPRODUCT formula is easier to work with.

However, I have not found or figured out a sum
formula
for
multple criteria. Any help out there?
Thanks
BobT




.



.

How about adding a unique pattern of spaces to the operator in the
formula and then doing a Replace?
Example: (^ are spaces)
=Sumproduct((Range1^=^Criteria1)*(Range2^^^Criter ia2)*(Range3^^^<^Criteria3))
This prevents the same operators in different locations from being
changed.

Thanks
I was resigned to writing a macro or manually using
replace to change the operator when needed. This tip will
be very helpful either way.

-----Original Message-----
How about adding a unique pattern of spaces to the
operator in the
formula and then doing a Replace?
Example: (^ are spaces)
=Sumproduct((Range1^=^Criteria1)*(Range2^^^Crite ria2)*
(Range3^^^<^Criteria3))
This prevents the same operators in different locations
from being
changed.

.