URGENT: sumproduct variation question
 

I'm currently extracting data from a larger table to a smaller summary table
(on a different tab) using the SUMPRODUCT function in this form (an example):

=SUMPRODUCT(--(CNTXTName="Shared
Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$14),CNTXTOrders)

to add the values of CNTXTOrders in the rows that meet the preceeding given
conditions in the formula.

QUESTION: I need to find a way to do the same operation but AVERAGE the
values (instead of summing them) and COUNT the occurence of values that are
equal to or less than 10.

Can anyone advise on a way to do this??? Unfortunately this is part of an
elaborate spreadsheet, so I'm structurally limited (can't rearrange the
data).

Any advice would be so much appreciated as I'm already past my deadline.

Many, many thanks,

Marika :) :)

URGENT: sumproduct variation question
 

try these ideas. You should be able to figure it out from there
maybe this ARRAY formula to be entered with control+shift+enter instead of
just enter

=COUNT(IF(($B$1:$B$5=B1)*($A$1:$A$5),1))
=COUNT(IF(A2:A4,1,B2:B4))
=AVERAGE(IF(rngSMALL(rng,4),IF(rng<LARGE(rng,4),r ng)))
=AVERAGE(IF((A1:A4="jones")*(C1:C40),C1:C4))
=AVERAGE(IF(K7:K220,K7:K22))
=AVERAGE(IF($H$3:$H$23=C11,$K$3:$K$23)


--
Don Guillett
SalesAid Software

"marika1981" wrote in message
...
I'm currently extracting data from a larger table to a smaller summary
table
(on a different tab) using the SUMPRODUCT function in this form (an
example):

=SUMPRODUCT(--(CNTXTName="Shared

Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)

to add the values of CNTXTOrders in the rows that meet the preceeding
given
conditions in the formula.

QUESTION: I need to find a way to do the same operation but AVERAGE the
values (instead of summing them) and COUNT the occurence of values that
are
equal to or less than 10.

Can anyone advise on a way to do this??? Unfortunately this is part of an
elaborate spreadsheet, so I'm structurally limited (can't rearrange the
data).

Any advice would be so much appreciated as I'm already past my deadline.

Many, many thanks,

Marika :) :)

URGENT: sumproduct variation question
 

Don, many thanks....unfortunately, from the equations you offered, I'm still
unable to figure out how to apply the formula to just those rows that meet
three or more conditions (as I showed in the SUMPRODUCT formula below). It's
akin to a triple vlookup, though no qualifiying data falls in the left
column. Once I've targeted those rows, then I need to perform AVERAGE,
COUNTIF, etc on a certain column...

Let me know if you have any ideas and forgive me if I misunderstood your
reponse.

Marika

"Don Guillett" wrote:

try these ideas. You should be able to figure it out from there
maybe this ARRAY formula to be entered with control+shift+enter instead of
just enter

=COUNT(IF(($B$1:$B$5=B1)*($A$1:$A$5),1))
=COUNT(IF(A2:A4,1,B2:B4))
=AVERAGE(IF(rngSMALL(rng,4),IF(rng<LARGE(rng,4),r ng)))
=AVERAGE(IF((A1:A4="jones")*(C1:C40),C1:C4))
=AVERAGE(IF(K7:K220,K7:K22))
=AVERAGE(IF($H$3:$H$23=C11,$K$3:$K$23)


--
Don Guillett
SalesAid Software

"marika1981" wrote in message
...
I'm currently extracting data from a larger table to a smaller summary
table
(on a different tab) using the SUMPRODUCT function in this form (an
example):

=SUMPRODUCT(--(CNTXTName="Shared

Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)

to add the values of CNTXTOrders in the rows that meet the preceeding
given
conditions in the formula.

QUESTION: I need to find a way to do the same operation but AVERAGE the
values (instead of summing them) and COUNT the occurence of values that
are
equal to or less than 10.

Can anyone advise on a way to do this??? Unfortunately this is part of an
elaborate spreadsheet, so I'm structurally limited (can't rearrange the
data).

Any advice would be so much appreciated as I'm already past my deadline.

Many, many thanks,

Marika :) :)

URGENT: sumproduct variation question
 

(Untested):

=SUMPRODUCT(--(CNTXTName="Shared
Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)/SUMPRODUCT(--(CNTXTName="Shared
Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4))

should give you the average.

=SUMPRODUCT(--(CNTXTName="Shared
Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10))

should give you the number of occurrences less than or equal to 10.

--

Vasant




"marika1981" wrote in message
...
I'm currently extracting data from a larger table to a smaller summary
table
(on a different tab) using the SUMPRODUCT function in this form (an
example):

=SUMPRODUCT(--(CNTXTName="Shared

Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)

to add the values of CNTXTOrders in the rows that meet the preceeding
given
conditions in the formula.

QUESTION: I need to find a way to do the same operation but AVERAGE the
values (instead of summing them) and COUNT the occurence of values that
are
equal to or less than 10.

Can anyone advise on a way to do this??? Unfortunately this is part of an
elaborate spreadsheet, so I'm structurally limited (can't rearrange the
data).

Any advice would be so much appreciated as I'm already past my deadline.

Many, many thanks,

Marika :) :)

URGENT: sumproduct variation question
 

Average :

=AVERAGE(IF(--(CNTXTName="Shared
Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1),CNTXTOrders))



"Vasant Nanavati" wrote:

(Untested):

=SUMPRODUCT(--(CNTXTName="Shared
Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)/SUMPRODUCT(--(CNTXTName="Shared
Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4))

should give you the average.

=SUMPRODUCT(--(CNTXTName="Shared
Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10))

should give you the number of occurrences less than or equal to 10.

--

Vasant




"marika1981" wrote in message
...
I'm currently extracting data from a larger table to a smaller summary
table
(on a different tab) using the SUMPRODUCT function in this form (an
example):

=SUMPRODUCT(--(CNTXTName="Shared

Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)

to add the values of CNTXTOrders in the rows that meet the preceeding
given
conditions in the formula.

QUESTION: I need to find a way to do the same operation but AVERAGE the
values (instead of summing them) and COUNT the occurence of values that
are
equal to or less than 10.

Can anyone advise on a way to do this??? Unfortunately this is part of an
elaborate spreadsheet, so I'm structurally limited (can't rearrange the
data).

Any advice would be so much appreciated as I'm already past my deadline.

Many, many thanks,

Marika :) :)

URGENT: sumproduct variation question
 

That would average all CNTXTOrders plus a bunch of 1's and Zero's. Plus why
use both mulitplication and double negatives?

--
Regards,
Tom Ogilvy

"Toppers" wrote in message
...
Average :

=AVERAGE(IF(--(CNTXTName="Shared

Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1
),CNTXTOrders))



"Vasant Nanavati" wrote:

(Untested):

=SUMPRODUCT(--(CNTXTName="Shared

Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)/SUMPRODUCT(--(CNTXTName="Shared

Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4))

should give you the average.

=SUMPRODUCT(--(CNTXTName="Shared

Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10))

should give you the number of occurrences less than or equal to 10.

--

Vasant




"marika1981" wrote in message
...
I'm currently extracting data from a larger table to a smaller summary
table
(on a different tab) using the SUMPRODUCT function in this form (an
example):

=SUMPRODUCT(--(CNTXTName="Shared


Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)

to add the values of CNTXTOrders in the rows that meet the preceeding
given
conditions in the formula.

QUESTION: I need to find a way to do the same operation but AVERAGE
the
values (instead of summing them) and COUNT the occurence of values
that
are
equal to or less than 10.

Can anyone advise on a way to do this??? Unfortunately this is part
of an
elaborate spreadsheet, so I'm structurally limited (can't rearrange
the
data).

Any advice would be so much appreciated as I'm already past my
deadline.

Many, many thanks,

Marika :) :)

URGENT: sumproduct variation question
 

Thank you so much Vasant and Toppers - your suggestions *virtually* did the
trick, though I have two small problems (one of which Tom alluded to)

1) When using the AVERAGE(IF) solution Toppers offered:

=AVERAGE(IF(--(CNTXTName="Shared
Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1),CNTXTOrders))

it does include all zero values.

2) When using the count solution from Vasant:

=SUMPRODUCT(--(CNTXTName="Shared
Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$14),--(CNTXTOrders<=10))

it counts all zero values and all blank cells.

Is there a way to specify 0 in the AVERAGE(IF) formula and 0 & <10 for the
count formula???

I can't thank you enough - you've saved my day!

Marilka :)


"Tom Ogilvy" wrote:

That would average all CNTXTOrders plus a bunch of 1's and Zero's. Plus why
use both mulitplication and double negatives?

--
Regards,
Tom Ogilvy

"Toppers" wrote in message
...
Average :

=AVERAGE(IF(--(CNTXTName="Shared

Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1
),CNTXTOrders))



"Vasant Nanavati" wrote:

(Untested):

=SUMPRODUCT(--(CNTXTName="Shared

Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)/SUMPRODUCT(--(CNTXTName="Shared

Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4))

should give you the average.

=SUMPRODUCT(--(CNTXTName="Shared

Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10))

should give you the number of occurrences less than or equal to 10.

--

Vasant




"marika1981" wrote in message
...
I'm currently extracting data from a larger table to a smaller summary
table
(on a different tab) using the SUMPRODUCT function in this form (an
example):

=SUMPRODUCT(--(CNTXTName="Shared


Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)

to add the values of CNTXTOrders in the rows that meet the preceeding
given
conditions in the formula.

QUESTION: I need to find a way to do the same operation but AVERAGE
the
values (instead of summing them) and COUNT the occurence of values
that
are
equal to or less than 10.

Can anyone advise on a way to do this??? Unfortunately this is part
of an
elaborate spreadsheet, so I'm structurally limited (can't rearrange
the
data).

Any advice would be so much appreciated as I'm already past my
deadline.

Many, many thanks,

Marika :) :)

URGENT: sumproduct variation question
 

Sorry:

=SUMPRODUCT(--(CNTXTName="Shared
Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders0),--(CNTXTOrders<10))

should work for your second requirement.

--

Vasant

"marika1981" wrote in message
...
Thank you so much Vasant and Toppers - your suggestions *virtually* did
the
trick, though I have two small problems (one of which Tom alluded to)

1) When using the AVERAGE(IF) solution Toppers offered:

=AVERAGE(IF(--(CNTXTName="Shared

Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1
),CNTXTOrders))

it does include all zero values.

2) When using the count solution from Vasant:

=SUMPRODUCT(--(CNTXTName="Shared

Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10))

it counts all zero values and all blank cells.

Is there a way to specify 0 in the AVERAGE(IF) formula and 0 & <10 for
the
count formula???

I can't thank you enough - you've saved my day!

Marilka :)


"Tom Ogilvy" wrote:

That would average all CNTXTOrders plus a bunch of 1's and Zero's. Plus
why
use both mulitplication and double negatives?

--
Regards,
Tom Ogilvy

"Toppers" wrote in message
...
Average :

=AVERAGE(IF(--(CNTXTName="Shared


Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1
),CNTXTOrders))



"Vasant Nanavati" wrote:

(Untested):

=SUMPRODUCT(--(CNTXTName="Shared


Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)/SUMPRODUCT(--(CNTXTName="Shared


Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4))

should give you the average.

=SUMPRODUCT(--(CNTXTName="Shared


Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10))

should give you the number of occurrences less than or equal to 10.

--

Vasant




"marika1981" wrote in message
...
I'm currently extracting data from a larger table to a smaller
summary
table
(on a different tab) using the SUMPRODUCT function in this form
(an
example):

=SUMPRODUCT(--(CNTXTName="Shared



Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)

to add the values of CNTXTOrders in the rows that meet the
preceeding
given
conditions in the formula.

QUESTION: I need to find a way to do the same operation but
AVERAGE
the
values (instead of summing them) and COUNT the occurence of values
that
are
equal to or less than 10.

Can anyone advise on a way to do this??? Unfortunately this is
part
of an
elaborate spreadsheet, so I'm structurally limited (can't
rearrange
the
data).

Any advice would be so much appreciated as I'm already past my
deadline.

Many, many thanks,

Marika :) :)

URGENT: sumproduct variation question
 

=SUMPRODUCT(--(CNTXTName="Shared
Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10),--(CNTXTOrders<0),--(CNTXTOrders<""))

I stand corrected on Toppers formula. I did not see the "IF" in the
formula. Anyway if you want to exclude zeros or blanks if a cell in
CNTXTOrders would otherwise be included, then:

=AVERAGE(IF((CNTXTName="Shared Hosting")*(CNTXTProd=$C253)*
(CNTXTMonthRange=E415)*(CNTXTYearRange=E$1)*(CNTXT Orders<0)*
(CNTXTOrders<""),CNTXTOrders)


--
Regards,
Tom Ogilvy


"marika1981" wrote in message
...
Thank you so much Vasant and Toppers - your suggestions *virtually* did
the
trick, though I have two small problems (one of which Tom alluded to)

1) When using the AVERAGE(IF) solution Toppers offered:

=AVERAGE(IF(--(CNTXTName="Shared

Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1
),CNTXTOrders))

it does include all zero values.

2) When using the count solution from Vasant:

=SUMPRODUCT(--(CNTXTName="Shared

Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10))

it counts all zero values and all blank cells.

Is there a way to specify 0 in the AVERAGE(IF) formula and 0 & <10 for
the
count formula???

I can't thank you enough - you've saved my day!

Marilka :)


"Tom Ogilvy" wrote:

That would average all CNTXTOrders plus a bunch of 1's and Zero's. Plus
why
use both mulitplication and double negatives?

--
Regards,
Tom Ogilvy

"Toppers" wrote in message
...
Average :

=AVERAGE(IF(--(CNTXTName="Shared


Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1
),CNTXTOrders))



"Vasant Nanavati" wrote:

(Untested):

=SUMPRODUCT(--(CNTXTName="Shared


Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)/SUMPRODUCT(--(CNTXTName="Shared


Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4))

should give you the average.

=SUMPRODUCT(--(CNTXTName="Shared


Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10))

should give you the number of occurrences less than or equal to 10.

--

Vasant




"marika1981" wrote in message
...
I'm currently extracting data from a larger table to a smaller
summary
table
(on a different tab) using the SUMPRODUCT function in this form
(an
example):

=SUMPRODUCT(--(CNTXTName="Shared



Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)

to add the values of CNTXTOrders in the rows that meet the
preceeding
given
conditions in the formula.

QUESTION: I need to find a way to do the same operation but
AVERAGE
the
values (instead of summing them) and COUNT the occurence of values
that
are
equal to or less than 10.

Can anyone advise on a way to do this??? Unfortunately this is
part
of an
elaborate spreadsheet, so I'm structurally limited (can't
rearrange
the
data).

Any advice would be so much appreciated as I'm already past my
deadline.

Many, many thanks,

Marika :) :)

URGENT: sumproduct variation question
 

OK Tom, let's call it quits as I didn't think about blanks and zeros when I
tested my offering. Must do better!

"Tom Ogilvy" wrote:

=SUMPRODUCT(--(CNTXTName="Shared
Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10),--(CNTXTOrders<0),--(CNTXTOrders<""))

I stand corrected on Toppers formula. I did not see the "IF" in the
formula. Anyway if you want to exclude zeros or blanks if a cell in
CNTXTOrders would otherwise be included, then:

=AVERAGE(IF((CNTXTName="Shared Hosting")*(CNTXTProd=$C253)*
(CNTXTMonthRange=E415)*(CNTXTYearRange=E$1)*(CNTXT Orders<0)*
(CNTXTOrders<""),CNTXTOrders)


--
Regards,
Tom Ogilvy


"marika1981" wrote in message
...
Thank you so much Vasant and Toppers - your suggestions *virtually* did
the
trick, though I have two small problems (one of which Tom alluded to)

1) When using the AVERAGE(IF) solution Toppers offered:

=AVERAGE(IF(--(CNTXTName="Shared

Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1
),CNTXTOrders))

it does include all zero values.

2) When using the count solution from Vasant:

=SUMPRODUCT(--(CNTXTName="Shared

Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10))

it counts all zero values and all blank cells.

Is there a way to specify 0 in the AVERAGE(IF) formula and 0 & <10 for
the
count formula???

I can't thank you enough - you've saved my day!

Marilka :)


"Tom Ogilvy" wrote:

That would average all CNTXTOrders plus a bunch of 1's and Zero's. Plus
why
use both mulitplication and double negatives?

--
Regards,
Tom Ogilvy

"Toppers" wrote in message
...
Average :

=AVERAGE(IF(--(CNTXTName="Shared


Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1
),CNTXTOrders))



"Vasant Nanavati" wrote:

(Untested):

=SUMPRODUCT(--(CNTXTName="Shared


Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)/SUMPRODUCT(--(CNTXTName="Shared


Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4))

should give you the average.

=SUMPRODUCT(--(CNTXTName="Shared


Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10))

should give you the number of occurrences less than or equal to 10.

--

Vasant




"marika1981" wrote in message
...
I'm currently extracting data from a larger table to a smaller
summary
table
(on a different tab) using the SUMPRODUCT function in this form
(an
example):

=SUMPRODUCT(--(CNTXTName="Shared



Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)

to add the values of CNTXTOrders in the rows that meet the
preceeding
given
conditions in the formula.

QUESTION: I need to find a way to do the same operation but
AVERAGE
the
values (instead of summing them) and COUNT the occurence of values
that
are
equal to or less than 10.

Can anyone advise on a way to do this??? Unfortunately this is
part
of an
elaborate spreadsheet, so I'm structurally limited (can't
rearrange
the
data).

Any advice would be so much appreciated as I'm already past my
deadline.

Many, many thanks,

Marika :) :)

URGENT: sumproduct variation question
 

What's that old saying, "after you my dear alphonse"

--
Don Guillett
SalesAid Software

"Toppers" wrote in message
...
OK Tom, let's call it quits as I didn't think about blanks and zeros when
I
tested my offering. Must do better!

"Tom Ogilvy" wrote:

=SUMPRODUCT(--(CNTXTName="Shared

Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10),--(CNTXTOrders<0),--(CNTXTOrders<""))

I stand corrected on Toppers formula. I did not see the "IF" in the
formula. Anyway if you want to exclude zeros or blanks if a cell in
CNTXTOrders would otherwise be included, then:

=AVERAGE(IF((CNTXTName="Shared Hosting")*(CNTXTProd=$C253)*
(CNTXTMonthRange=E415)*(CNTXTYearRange=E$1)*(CNTXT Orders<0)*
(CNTXTOrders<""),CNTXTOrders)


--
Regards,
Tom Ogilvy


"marika1981" wrote in message
...
Thank you so much Vasant and Toppers - your suggestions *virtually*
did
the
trick, though I have two small problems (one of which Tom alluded to)

1) When using the AVERAGE(IF) solution Toppers offered:

=AVERAGE(IF(--(CNTXTName="Shared


Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1
),CNTXTOrders))

it does include all zero values.

2) When using the count solution from Vasant:

=SUMPRODUCT(--(CNTXTName="Shared


Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10))

it counts all zero values and all blank cells.

Is there a way to specify 0 in the AVERAGE(IF) formula and 0 & <10
for
the
count formula???

I can't thank you enough - you've saved my day!

Marilka :)


"Tom Ogilvy" wrote:

That would average all CNTXTOrders plus a bunch of 1's and Zero's.
Plus
why
use both mulitplication and double negatives?

--
Regards,
Tom Ogilvy

"Toppers" wrote in message
...
Average :

=AVERAGE(IF(--(CNTXTName="Shared



Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1
),CNTXTOrders))



"Vasant Nanavati" wrote:

(Untested):

=SUMPRODUCT(--(CNTXTName="Shared



Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)/SUMPRODUCT(--(CNTXTName="Shared



Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4))

should give you the average.

=SUMPRODUCT(--(CNTXTName="Shared



Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10))

should give you the number of occurrences less than or equal to
10.

--

Vasant




"marika1981" wrote in
message
...
I'm currently extracting data from a larger table to a smaller
summary
table
(on a different tab) using the SUMPRODUCT function in this
form
(an
example):

=SUMPRODUCT(--(CNTXTName="Shared




Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)

to add the values of CNTXTOrders in the rows that meet the
preceeding
given
conditions in the formula.

QUESTION: I need to find a way to do the same operation but
AVERAGE
the
values (instead of summing them) and COUNT the occurence of
values
that
are
equal to or less than 10.

Can anyone advise on a way to do this??? Unfortunately this
is
part
of an
elaborate spreadsheet, so I'm structurally limited (can't
rearrange
the
data).

Any advice would be so much appreciated as I'm already past my
deadline.

Many, many thanks,

Marika :) :)

URGENT: sumproduct variation question
 

Thank you all so much for your help.....I *very* much appreciate it.

However, I'm still unable to get the AVERAGE formula to ignore zero values.
I've checked the formula I entered four or five times - here it is on the tab
i'm currently working on:

=AVERAGE(IF((KREMonthRange=F$16)*(KREYearRange=F$1 5)*(KREAltaVista<0)*(KREAltaVista<""),(KREAltaVi sta)))

(I do get an answer - but it's the average including zeros)

What have I got wrong?????

Marika :)



"Tom Ogilvy" wrote:

=SUMPRODUCT(--(CNTXTName="Shared
Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10),--(CNTXTOrders<0),--(CNTXTOrders<""))

I stand corrected on Toppers formula. I did not see the "IF" in the
formula. Anyway if you want to exclude zeros or blanks if a cell in
CNTXTOrders would otherwise be included, then:

=AVERAGE(IF((CNTXTName="Shared Hosting")*(CNTXTProd=$C253)*
(CNTXTMonthRange=E415)*(CNTXTYearRange=E$1)*(CNTXT Orders<0)*
(CNTXTOrders<""),CNTXTOrders)


--
Regards,
Tom Ogilvy


"marika1981" wrote in message
...
Thank you so much Vasant and Toppers - your suggestions *virtually* did
the
trick, though I have two small problems (one of which Tom alluded to)

1) When using the AVERAGE(IF) solution Toppers offered:

=AVERAGE(IF(--(CNTXTName="Shared

Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1
),CNTXTOrders))

it does include all zero values.

2) When using the count solution from Vasant:

=SUMPRODUCT(--(CNTXTName="Shared

Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10))

it counts all zero values and all blank cells.

Is there a way to specify 0 in the AVERAGE(IF) formula and 0 & <10 for
the
count formula???

I can't thank you enough - you've saved my day!

Marilka :)


"Tom Ogilvy" wrote:

That would average all CNTXTOrders plus a bunch of 1's and Zero's. Plus
why
use both mulitplication and double negatives?

--
Regards,
Tom Ogilvy

"Toppers" wrote in message
...
Average :

=AVERAGE(IF(--(CNTXTName="Shared


Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1
),CNTXTOrders))



"Vasant Nanavati" wrote:

(Untested):

=SUMPRODUCT(--(CNTXTName="Shared


Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)/SUMPRODUCT(--(CNTXTName="Shared


Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4))

should give you the average.

=SUMPRODUCT(--(CNTXTName="Shared


Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10))

should give you the number of occurrences less than or equal to 10.

--

Vasant




"marika1981" wrote in message
...
I'm currently extracting data from a larger table to a smaller
summary
table
(on a different tab) using the SUMPRODUCT function in this form
(an
example):

=SUMPRODUCT(--(CNTXTName="Shared



Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)

to add the values of CNTXTOrders in the rows that meet the
preceeding
given
conditions in the formula.

QUESTION: I need to find a way to do the same operation but
AVERAGE
the
values (instead of summing them) and COUNT the occurence of values
that
are
equal to or less than 10.

Can anyone advise on a way to do this??? Unfortunately this is
part
of an
elaborate spreadsheet, so I'm structurally limited (can't
rearrange
the
data).

Any advice would be so much appreciated as I'm already past my
deadline.

Many, many thanks,

Marika :) :)

URGENT: sumproduct variation question
 

Hi again,
It's late here ... but I can't see anything wrong with the
formula. I have retested your originals with Tom's changes and they work
fine. I then modified the original AVERAGE one to reflect your new one and
it also works OK: if I take out the '<0' test then 0s are included (as
expected) but removed if the test is included.



"marika1981" wrote:

Thank you all so much for your help.....I *very* much appreciate it.

However, I'm still unable to get the AVERAGE formula to ignore zero values.
I've checked the formula I entered four or five times - here it is on the tab
i'm currently working on:

=AVERAGE(IF((KREMonthRange=F$16)*(KREYearRange=F$1 5)*(KREAltaVista<0)*(KREAltaVista<""),(KREAltaVi sta)))

(I do get an answer - but it's the average including zeros)

What have I got wrong?????

Marika :)



"Tom Ogilvy" wrote:

=SUMPRODUCT(--(CNTXTName="Shared
Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10),--(CNTXTOrders<0),--(CNTXTOrders<""))

I stand corrected on Toppers formula. I did not see the "IF" in the
formula. Anyway if you want to exclude zeros or blanks if a cell in
CNTXTOrders would otherwise be included, then:

=AVERAGE(IF((CNTXTName="Shared Hosting")*(CNTXTProd=$C253)*
(CNTXTMonthRange=E415)*(CNTXTYearRange=E$1)*(CNTXT Orders<0)*
(CNTXTOrders<""),CNTXTOrders)


--
Regards,
Tom Ogilvy


"marika1981" wrote in message
...
Thank you so much Vasant and Toppers - your suggestions *virtually* did
the
trick, though I have two small problems (one of which Tom alluded to)

1) When using the AVERAGE(IF) solution Toppers offered:

=AVERAGE(IF(--(CNTXTName="Shared

Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1
),CNTXTOrders))

it does include all zero values.

2) When using the count solution from Vasant:

=SUMPRODUCT(--(CNTXTName="Shared

Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10))

it counts all zero values and all blank cells.

Is there a way to specify 0 in the AVERAGE(IF) formula and 0 & <10 for
the
count formula???

I can't thank you enough - you've saved my day!

Marilka :)


"Tom Ogilvy" wrote:

That would average all CNTXTOrders plus a bunch of 1's and Zero's. Plus
why
use both mulitplication and double negatives?

--
Regards,
Tom Ogilvy

"Toppers" wrote in message
...
Average :

=AVERAGE(IF(--(CNTXTName="Shared


Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1
),CNTXTOrders))



"Vasant Nanavati" wrote:

(Untested):

=SUMPRODUCT(--(CNTXTName="Shared


Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)/SUMPRODUCT(--(CNTXTName="Shared


Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4))

should give you the average.

=SUMPRODUCT(--(CNTXTName="Shared


Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10))

should give you the number of occurrences less than or equal to 10.

--

Vasant




"marika1981" wrote in message
...
I'm currently extracting data from a larger table to a smaller
summary
table
(on a different tab) using the SUMPRODUCT function in this form
(an
example):

=SUMPRODUCT(--(CNTXTName="Shared



Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)

to add the values of CNTXTOrders in the rows that meet the
preceeding
given
conditions in the formula.

QUESTION: I need to find a way to do the same operation but
AVERAGE
the
values (instead of summing them) and COUNT the occurence of values
that
are
equal to or less than 10.

Can anyone advise on a way to do this??? Unfortunately this is
part
of an
elaborate spreadsheet, so I'm structurally limited (can't
rearrange
the
data).

Any advice would be so much appreciated as I'm already past my
deadline.

Many, many thanks,

Marika :) :)

URGENT: sumproduct variation question
 

Wouldn't this be far simpler to solve by treating the source as a
database and using the very powerful techniques developed over the
decades for data access and analysis?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I'm currently extracting data from a larger table to a smaller summary table
(on a different tab) using the SUMPRODUCT function in this form (an example):

=SUMPRODUCT(--(CNTXTName="Shared
Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$14),CNTXTOrders)

to add the values of CNTXTOrders in the rows that meet the preceeding given
conditions in the formula.

QUESTION: I need to find a way to do the same operation but AVERAGE the
values (instead of summing them) and COUNT the occurence of values that are
equal to or less than 10.

Can anyone advise on a way to do this??? Unfortunately this is part of an
elaborate spreadsheet, so I'm structurally limited (can't rearrange the
data).

Any advice would be so much appreciated as I'm already past my deadline.

Many, many thanks,

Marika :) :)