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!

Try these:

With
G4:G780 containing numbers

Then
A1: 100
A2: 500

This formula counts the G4:G780 values that are between 100 and 500,
inclusive.
A3: =COUNTIF(G4:G780,"="&A1)-COUNTIF(G4:G780,""&A2)

This formula sums those amounts:
A4: =SUMPRODUCT((G4:G780=A1)*(G4:G780<=A2)*(G4:G780))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"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!

"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!

opps another typo,
place the 1st sum formula for counting on cell G2
place the 2nd sum formula for summing values on cell G3..

mine driller

"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!

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!

"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!

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!

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!

Ron:

Perfect! Many thannks for your help!

"Ron Coderre" wrote:

Try these:

With
G4:G780 containing numbers

Then
A1: 100
A2: 500

This formula counts the G4:G780 values that are between 100 and 500,
inclusive.
A3: =COUNTIF(G4:G780,"="&A1)-COUNTIF(G4:G780,""&A2)

This formula sums those amounts:
A4: =SUMPRODUCT((G4:G780=A1)*(G4:G780<=A2)*(G4:G780))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"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!

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!

Thanks for the feedback, Jack....I'm glad I could help.

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

XL2002, WinXP


"Jack Taylor" wrote:

Ron:

Perfect! Many thannks for your help!

"Ron Coderre" wrote:

Try these:

With
G4:G780 containing numbers

Then
A1: 100
A2: 500

This formula counts the G4:G780 values that are between 100 and 500,
inclusive.
A3: =COUNTIF(G4:G780,"="&A1)-COUNTIF(G4:G780,""&A2)

This formula sums those amounts:
A4: =SUMPRODUCT((G4:G780=A1)*(G4:G780<=A2)*(G4:G780))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"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!

Ron:

One more question:

I need to do the same thing in a column whose cells are products of another
formula. For example:

Cells H4 through H766 contain a formula that calculates the percentage
increase or decrease of contributions from the year before. I'd like to be
able to find a formula that calculates the total number of decreases...the
total number the same as last year...and the total number of actual increases.

THanks again!

JT




"Ron Coderre" wrote:

Try these:

With
G4:G780 containing numbers

Then
A1: 100
A2: 500

This formula counts the G4:G780 values that are between 100 and 500,
inclusive.
A3: =COUNTIF(G4:G780,"="&A1)-COUNTIF(G4:G780,""&A2)

This formula sums those amounts:
A4: =SUMPRODUCT((G4:G780=A1)*(G4:G780<=A2)*(G4:G780))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"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!

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!

Try this:

With
G4:G780 containing Contribution Amount
H4:H780 containing Inc(Dec) from Previous

These formula calculate the total and counts for those who contributed
-----------------------
LESS than previous:
The Total
=SUMIF(H4:H780,"<0",G4:G780)
The Count
=COUNTIF(H4:H780,"<0")

-----------------------
SAME as previous:
The Total
=SUMIF(H4:H780,"=0",G4:G780)
The Count
=COUNTIF(H4:H780,"=0")

-----------------------
MORE THAN previous:
The Total
=SUMIF(H4:H780,"0",G4:G780)
The Count
=COUNTIF(H4:H780,"0")
-----------------------

Do those help?
***********
Regards,
Ron

XL2002, WinXP


"Jack Taylor" wrote:

Ron:

One more question:

I need to do the same thing in a column whose cells are products of another
formula. For example:

Cells H4 through H766 contain a formula that calculates the percentage
increase or decrease of contributions from the year before. I'd like to be
able to find a formula that calculates the total number of decreases...the
total number the same as last year...and the total number of actual increases.

THanks again!

JT




"Ron Coderre" wrote:

Try these:

With
G4:G780 containing numbers

Then
A1: 100
A2: 500

This formula counts the G4:G780 values that are between 100 and 500,
inclusive.
A3: =COUNTIF(G4:G780,"="&A1)-COUNTIF(G4:G780,""&A2)

This formula sums those amounts:
A4: =SUMPRODUCT((G4:G780=A1)*(G4:G780<=A2)*(G4:G780))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"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!

Ron:

Once again it worked perfectly. Can I ask one more question...then I
promise I'll shut up?

I need one more formula that will do the following:

Column F4 through F766 contains contributions for the year 2006. SOme of
those who made contributions in 2006 have NOT contributed for 2007. What
formula can I use to count those individuals who have NOT yet contributed for
2007, along with a sum of their 2006 contributions?


"Ron Coderre" wrote:

Try this:

With
G4:G780 containing Contribution Amount
H4:H780 containing Inc(Dec) from Previous

These formula calculate the total and counts for those who contributed
-----------------------
LESS than previous:
The Total
=SUMIF(H4:H780,"<0",G4:G780)
The Count
=COUNTIF(H4:H780,"<0")

-----------------------
SAME as previous:
The Total
=SUMIF(H4:H780,"=0",G4:G780)
The Count
=COUNTIF(H4:H780,"=0")

-----------------------
MORE THAN previous:
The Total
=SUMIF(H4:H780,"0",G4:G780)
The Count
=COUNTIF(H4:H780,"0")
-----------------------

Do those help?
***********
Regards,
Ron

XL2002, WinXP


"Jack Taylor" wrote:

Ron:

One more question:

I need to do the same thing in a column whose cells are products of another
formula. For example:

Cells H4 through H766 contain a formula that calculates the percentage
increase or decrease of contributions from the year before. I'd like to be
able to find a formula that calculates the total number of decreases...the
total number the same as last year...and the total number of actual increases.

THanks again!

JT




"Ron Coderre" wrote:

Try these:

With
G4:G780 containing numbers

Then
A1: 100
A2: 500

This formula counts the G4:G780 values that are between 100 and 500,
inclusive.
A3: =COUNTIF(G4:G780,"="&A1)-COUNTIF(G4:G780,""&A2)

This formula sums those amounts:
A4: =SUMPRODUCT((G4:G780=A1)*(G4:G780<=A2)*(G4:G780))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"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!

Maybe something like this:

With
F4:F766 containing 2006 contributions (or blanks? zeroes?)
G4:G766 containing 2007 contributions

Then...this formula counts the number of first time contributors for 2007
where the 2006 cell is blank
=SUMPRODUCT(ISBLANK(F4:F766)*(G4:G7660))

or...if the 2006 non-contributor cells contain zeroes OR blanks
=SUMPRODUCT((F4:F766=0)*(G4:G7660))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Jack Taylor" wrote:

Ron:

Once again it worked perfectly. Can I ask one more question...then I
promise I'll shut up?

I need one more formula that will do the following:

Column F4 through F766 contains contributions for the year 2006. SOme of
those who made contributions in 2006 have NOT contributed for 2007. What
formula can I use to count those individuals who have NOT yet contributed for
2007, along with a sum of their 2006 contributions?


"Ron Coderre" wrote:

Try this:

With
G4:G780 containing Contribution Amount
H4:H780 containing Inc(Dec) from Previous

These formula calculate the total and counts for those who contributed
-----------------------
LESS than previous:
The Total
=SUMIF(H4:H780,"<0",G4:G780)
The Count
=COUNTIF(H4:H780,"<0")

-----------------------
SAME as previous:
The Total
=SUMIF(H4:H780,"=0",G4:G780)
The Count
=COUNTIF(H4:H780,"=0")

-----------------------
MORE THAN previous:
The Total
=SUMIF(H4:H780,"0",G4:G780)
The Count
=COUNTIF(H4:H780,"0")
-----------------------

Do those help?
***********
Regards,
Ron

XL2002, WinXP


"Jack Taylor" wrote:

Ron:

One more question:

I need to do the same thing in a column whose cells are products of another
formula. For example:

Cells H4 through H766 contain a formula that calculates the percentage
increase or decrease of contributions from the year before. I'd like to be
able to find a formula that calculates the total number of decreases...the
total number the same as last year...and the total number of actual increases.

THanks again!

JT




"Ron Coderre" wrote:

Try these:

With
G4:G780 containing numbers

Then
A1: 100
A2: 500

This formula counts the G4:G780 values that are between 100 and 500,
inclusive.
A3: =COUNTIF(G4:G780,"="&A1)-COUNTIF(G4:G780,""&A2)

This formula sums those amounts:
A4: =SUMPRODUCT((G4:G780=A1)*(G4:G780<=A2)*(G4:G780))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"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!

Ooops! I sure didn't read your latest post correctly!

You wanted the count and total 2006 contributions for those who contributed
in 2006, but haven't contributed for 2007, right?

Where
F4:F766 containing 2006 contributions (or blanks? zeroes?)
G4:G766 containing 2007 contributions (or blanks? zeroes?)

Try this:

The count
=SUMPRODUCT((F4:F7660)*(G4:G766=0))

Their total 2006 contributions
=SUMPRODUCT((F4:F7660)*(G4:G766=0)*(F4:F766))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Jack Taylor" wrote:

Ron:

Once again it worked perfectly. Can I ask one more question...then I
promise I'll shut up?

I need one more formula that will do the following:

Column F4 through F766 contains contributions for the year 2006. SOme of
those who made contributions in 2006 have NOT contributed for 2007. What
formula can I use to count those individuals who have NOT yet contributed for
2007, along with a sum of their 2006 contributions?


"Ron Coderre" wrote:

Try this:

With
G4:G780 containing Contribution Amount
H4:H780 containing Inc(Dec) from Previous

These formula calculate the total and counts for those who contributed
-----------------------
LESS than previous:
The Total
=SUMIF(H4:H780,"<0",G4:G780)
The Count
=COUNTIF(H4:H780,"<0")

-----------------------
SAME as previous:
The Total
=SUMIF(H4:H780,"=0",G4:G780)
The Count
=COUNTIF(H4:H780,"=0")

-----------------------
MORE THAN previous:
The Total
=SUMIF(H4:H780,"0",G4:G780)
The Count
=COUNTIF(H4:H780,"0")
-----------------------

Do those help?
***********
Regards,
Ron

XL2002, WinXP


"Jack Taylor" wrote:

Ron:

One more question:

I need to do the same thing in a column whose cells are products of another
formula. For example:

Cells H4 through H766 contain a formula that calculates the percentage
increase or decrease of contributions from the year before. I'd like to be
able to find a formula that calculates the total number of decreases...the
total number the same as last year...and the total number of actual increases.

THanks again!

JT




"Ron Coderre" wrote:

Try these:

With
G4:G780 containing numbers

Then
A1: 100
A2: 500

This formula counts the G4:G780 values that are between 100 and 500,
inclusive.
A3: =COUNTIF(G4:G780,"="&A1)-COUNTIF(G4:G780,""&A2)

This formula sums those amounts:
A4: =SUMPRODUCT((G4:G780=A1)*(G4:G780<=A2)*(G4:G780))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"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!

No...neither of those return what I am looking for. I tried
=COUNTIF(F4:F766,"")-COUNTIF(G4:G766,"") but the total is not accurate (off
by around 14 or so).

"Ron Coderre" wrote:

Maybe something like this:

With
F4:F766 containing 2006 contributions (or blanks? zeroes?)
G4:G766 containing 2007 contributions

Then...this formula counts the number of first time contributors for 2007
where the 2006 cell is blank
=SUMPRODUCT(ISBLANK(F4:F766)*(G4:G7660))

or...if the 2006 non-contributor cells contain zeroes OR blanks
=SUMPRODUCT((F4:F766=0)*(G4:G7660))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Jack Taylor" wrote:

Ron:

Once again it worked perfectly. Can I ask one more question...then I
promise I'll shut up?

I need one more formula that will do the following:

Column F4 through F766 contains contributions for the year 2006. SOme of
those who made contributions in 2006 have NOT contributed for 2007. What
formula can I use to count those individuals who have NOT yet contributed for
2007, along with a sum of their 2006 contributions?


"Ron Coderre" wrote:

Try this:

With
G4:G780 containing Contribution Amount
H4:H780 containing Inc(Dec) from Previous

These formula calculate the total and counts for those who contributed
-----------------------
LESS than previous:
The Total
=SUMIF(H4:H780,"<0",G4:G780)
The Count
=COUNTIF(H4:H780,"<0")

-----------------------
SAME as previous:
The Total
=SUMIF(H4:H780,"=0",G4:G780)
The Count
=COUNTIF(H4:H780,"=0")

-----------------------
MORE THAN previous:
The Total
=SUMIF(H4:H780,"0",G4:G780)
The Count
=COUNTIF(H4:H780,"0")
-----------------------

Do those help?
***********
Regards,
Ron

XL2002, WinXP


"Jack Taylor" wrote:

Ron:

One more question:

I need to do the same thing in a column whose cells are products of another
formula. For example:

Cells H4 through H766 contain a formula that calculates the percentage
increase or decrease of contributions from the year before. I'd like to be
able to find a formula that calculates the total number of decreases...the
total number the same as last year...and the total number of actual increases.

THanks again!

JT




"Ron Coderre" wrote:

Try these:

With
G4:G780 containing numbers

Then
A1: 100
A2: 500

This formula counts the G4:G780 values that are between 100 and 500,
inclusive.
A3: =COUNTIF(G4:G780,"="&A1)-COUNTIF(G4:G780,""&A2)

This formula sums those amounts:
A4: =SUMPRODUCT((G4:G780=A1)*(G4:G780<=A2)*(G4:G780))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"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!

We're better...but I'm off on the count by 4 (formula counts 4 higher that
does an actual sort). Trying to find where the problem is. If the cells in
the G column are blank (contain no zeros) should I express that fact in the
formula?

"Ron Coderre" wrote:

Ooops! I sure didn't read your latest post correctly!

You wanted the count and total 2006 contributions for those who contributed
in 2006, but haven't contributed for 2007, right?

Where
F4:F766 containing 2006 contributions (or blanks? zeroes?)
G4:G766 containing 2007 contributions (or blanks? zeroes?)

Try this:

The count
=SUMPRODUCT((F4:F7660)*(G4:G766=0))

Their total 2006 contributions
=SUMPRODUCT((F4:F7660)*(G4:G766=0)*(F4:F766))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Jack Taylor" wrote:

Ron:

Once again it worked perfectly. Can I ask one more question...then I
promise I'll shut up?

I need one more formula that will do the following:

Column F4 through F766 contains contributions for the year 2006. SOme of
those who made contributions in 2006 have NOT contributed for 2007. What
formula can I use to count those individuals who have NOT yet contributed for
2007, along with a sum of their 2006 contributions?


"Ron Coderre" wrote:

Try this:

With
G4:G780 containing Contribution Amount
H4:H780 containing Inc(Dec) from Previous

These formula calculate the total and counts for those who contributed
-----------------------
LESS than previous:
The Total
=SUMIF(H4:H780,"<0",G4:G780)
The Count
=COUNTIF(H4:H780,"<0")

-----------------------
SAME as previous:
The Total
=SUMIF(H4:H780,"=0",G4:G780)
The Count
=COUNTIF(H4:H780,"=0")

-----------------------
MORE THAN previous:
The Total
=SUMIF(H4:H780,"0",G4:G780)
The Count
=COUNTIF(H4:H780,"0")
-----------------------

Do those help?
***********
Regards,
Ron

XL2002, WinXP


"Jack Taylor" wrote:

Ron:

One more question:

I need to do the same thing in a column whose cells are products of another
formula. For example:

Cells H4 through H766 contain a formula that calculates the percentage
increase or decrease of contributions from the year before. I'd like to be
able to find a formula that calculates the total number of decreases...the
total number the same as last year...and the total number of actual increases.

THanks again!

JT




"Ron Coderre" wrote:

Try these:

With
G4:G780 containing numbers

Then
A1: 100
A2: 500

This formula counts the G4:G780 values that are between 100 and 500,
inclusive.
A3: =COUNTIF(G4:G780,"="&A1)-COUNTIF(G4:G780,""&A2)

This formula sums those amounts:
A4: =SUMPRODUCT((G4:G780=A1)*(G4:G780<=A2)*(G4:G780))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"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!

I found the problem. I need the formula to look for a BLANK in the G column
instead of being equal to 0.

"Ron Coderre" wrote:

Ooops! I sure didn't read your latest post correctly!

You wanted the count and total 2006 contributions for those who contributed
in 2006, but haven't contributed for 2007, right?

Where
F4:F766 containing 2006 contributions (or blanks? zeroes?)
G4:G766 containing 2007 contributions (or blanks? zeroes?)

Try this:

The count
=SUMPRODUCT((F4:F7660)*(G4:G766=0))

Their total 2006 contributions
=SUMPRODUCT((F4:F7660)*(G4:G766=0)*(F4:F766))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Jack Taylor" wrote:

Ron:

Once again it worked perfectly. Can I ask one more question...then I
promise I'll shut up?

I need one more formula that will do the following:

Column F4 through F766 contains contributions for the year 2006. SOme of
those who made contributions in 2006 have NOT contributed for 2007. What
formula can I use to count those individuals who have NOT yet contributed for
2007, along with a sum of their 2006 contributions?


"Ron Coderre" wrote:

Try this:

With
G4:G780 containing Contribution Amount
H4:H780 containing Inc(Dec) from Previous

These formula calculate the total and counts for those who contributed
-----------------------
LESS than previous:
The Total
=SUMIF(H4:H780,"<0",G4:G780)
The Count
=COUNTIF(H4:H780,"<0")

-----------------------
SAME as previous:
The Total
=SUMIF(H4:H780,"=0",G4:G780)
The Count
=COUNTIF(H4:H780,"=0")

-----------------------
MORE THAN previous:
The Total
=SUMIF(H4:H780,"0",G4:G780)
The Count
=COUNTIF(H4:H780,"0")
-----------------------

Do those help?
***********
Regards,
Ron

XL2002, WinXP


"Jack Taylor" wrote:

Ron:

One more question:

I need to do the same thing in a column whose cells are products of another
formula. For example:

Cells H4 through H766 contain a formula that calculates the percentage
increase or decrease of contributions from the year before. I'd like to be
able to find a formula that calculates the total number of decreases...the
total number the same as last year...and the total number of actual increases.

THanks again!

JT




"Ron Coderre" wrote:

Try these:

With
G4:G780 containing numbers

Then
A1: 100
A2: 500

This formula counts the G4:G780 values that are between 100 and 500,
inclusive.
A3: =COUNTIF(G4:G780,"="&A1)-COUNTIF(G4:G780,""&A2)

This formula sums those amounts:
A4: =SUMPRODUCT((G4:G780=A1)*(G4:G780<=A2)*(G4:G780))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"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!

Thanks for the feedback.....I"m glad you got that to work for you.

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

XL2002, WinXP


"Jack Taylor" wrote:

I found the problem. I need the formula to look for a BLANK in the G column
instead of being equal to 0.

"Ron Coderre" wrote:

Ooops! I sure didn't read your latest post correctly!

You wanted the count and total 2006 contributions for those who contributed
in 2006, but haven't contributed for 2007, right?

Where
F4:F766 containing 2006 contributions (or blanks? zeroes?)
G4:G766 containing 2007 contributions (or blanks? zeroes?)

Try this:

The count
=SUMPRODUCT((F4:F7660)*(G4:G766=0))

Their total 2006 contributions
=SUMPRODUCT((F4:F7660)*(G4:G766=0)*(F4:F766))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Jack Taylor" wrote:

Ron:

Once again it worked perfectly. Can I ask one more question...then I
promise I'll shut up?

I need one more formula that will do the following:

Column F4 through F766 contains contributions for the year 2006. SOme of
those who made contributions in 2006 have NOT contributed for 2007. What
formula can I use to count those individuals who have NOT yet contributed for
2007, along with a sum of their 2006 contributions?


"Ron Coderre" wrote:

Try this:

With
G4:G780 containing Contribution Amount
H4:H780 containing Inc(Dec) from Previous

These formula calculate the total and counts for those who contributed
-----------------------
LESS than previous:
The Total
=SUMIF(H4:H780,"<0",G4:G780)
The Count
=COUNTIF(H4:H780,"<0")

-----------------------
SAME as previous:
The Total
=SUMIF(H4:H780,"=0",G4:G780)
The Count
=COUNTIF(H4:H780,"=0")

-----------------------
MORE THAN previous:
The Total
=SUMIF(H4:H780,"0",G4:G780)
The Count
=COUNTIF(H4:H780,"0")
-----------------------

Do those help?
***********
Regards,
Ron

XL2002, WinXP


"Jack Taylor" wrote:

Ron:

One more question:

I need to do the same thing in a column whose cells are products of another
formula. For example:

Cells H4 through H766 contain a formula that calculates the percentage
increase or decrease of contributions from the year before. I'd like to be
able to find a formula that calculates the total number of decreases...the
total number the same as last year...and the total number of actual increases.

THanks again!

JT




"Ron Coderre" wrote:

Try these:

With
G4:G780 containing numbers

Then
A1: 100
A2: 500

This formula counts the G4:G780 values that are between 100 and 500,
inclusive.
A3: =COUNTIF(G4:G780,"="&A1)-COUNTIF(G4:G780,""&A2)

This formula sums those amounts:
A4: =SUMPRODUCT((G4:G780=A1)*(G4:G780<=A2)*(G4:G780))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"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!