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!