Again Mr. Ron
"Excel has facility to correct wrong entry of array, good to audit any user's
formulation entry which is very important." and eliminate over-confidence.
thanks for reading...

"Ron Coderre" wrote:

I see no point in educating users in less intuitive methods that could pose
maintenance problems when superior methods are available. There is nothing
special a user has to remember to use the SUMPRODUCT function, whereas the
array version of SUM requires [ctrl]+[shift]+[enter] to achieve the same
effect...a technique, by the way, that most users rarely need to use.

It's one thing to emphasize a non-intuitive technique when it is the only
way to solve a problem, but it is quite something else to insist on using
that technique when it either adds no value or, worse, makes the job more
difficult. It would be like forcing everyone to exit your home by climbing
out a window and sliding down a drainpipe when you have a perfectly useable
front door. Yes, it can be done that way, but why would anybody want to?

Have a nice day.
***********
Regards,
Ron

XL2002, WinXP


"driller" wrote:

Many users who are unfamiliar with CSE cannot figure out why array formulas
stop working after they edit them.

braced formula {....} are entered by C+S+E by the User..in a game of cards,
it's his/her Ace!

Array formulas are not supposed to be edited especially when the array
formulation is built with namedefined references.

let's all be familiar now, as my suggestion, but not as a rule - to orient
array formulation using other availed different functions of excel, not to be
dependent under one function like sumproduct - which offend the rules of
entering an array as told on the help files.

Excel has facility to correct wrong entry of array, good to audit any user's
formulation entry which is very important.

mine driller...for free.

"Ron Coderre" wrote:

I mean this:

In your array formula:
=SUM((G4:G780=G1)*(G4:G780<=H1)*(G4:G780))

you could use SUMPRODUCT, instead of SUM, and you wouldn't need to use CSE
at all:
=SUMPRODUCT((G4:G780=G1)*(G4:G780<=H1)*(G4:G780))

(Many users who are unfamiliar with CSE cannot figure out why array formulas
stop working after they edit them. My general rule is to only use array
formulas if they are the ONLY viable options.

I hope that helps.

***********
Regards,
Ron

XL2002, WinXP


"driller" wrote:

"In many cases, it replaces the array version of SUM"

Replace means something is in-there. I am confused.

Is it like a hat replaced by another hat ? Is that what you mean ?

please clarify and thanks for reminding.

"Ron Coderre" wrote:

Regarding:
I suggest you try now to start familiarizing your
workbook with Excel Array Sum Function

Not really necessary in this instance.

You might want to take a few minutes and investigate the SUMPRODUCT
function. In many cases, it replaces the array version of SUM.

***********
Regards,
Ron

XL2002, WinXP


"driller" wrote:

"I would also like a similar formula that totals the values in that same
range."

I suggest you try now to start familiarizing your workbook with Excel Array
Sum Function.

using array sum function of excel with ctrl-shift-enter validation..

?NO.1
to COUNT the cells within G4:G780 between 100 and 500
G1 = 100 H1=500
on G2
= SUM((G4:G780=G1)*(G4:G780<=H1))
on edit mode *press ctrl-shift-enter* excel will check the array
formula...and will look like this, dont place the braces {}..
{= SUM((G4:G780=G1)*(G4:G780<=H1)}

?NO.2
to SUM the values on cells within G4:G780 between 100 and 500
G1 = 100 H1=500
on G2
= SUM((G4:G780=G1)*(G4:G780<=H1)*(G4:G780))
on edit mode *press ctrl-shift-enter* excel will check the array
formula...and will look like this, dont place the braces {}..
{= SUM((G4:G780=G1)*(G4:G780<=H1)*(G4:G780))}

"Jack Taylor" wrote:

I have a spreadsheet that contains contribution data. Each cell in the range
G4 through G780 contain individual contribution amounts.

I would like be able to devise a formula that looks at that entire range,
and then counts the number of occurrances in a certain range. For example:

I need the formula to look at the entire range (G4 through G780), find all
occurrances of values that occur between the values 100 and 500, then produce
a count of those that fall within that range. I would also like a similar
formula that totals the values in that same range.

Any help is GREATLY appreciated.

Happy Thanksgiving!