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 :) :)
|