Counting cells with a calculated value.

  • Thread starter Thread starter dugasyl
  • Start date Start date
D

dugasyl

I have been trying without success to do the following in 2007:

Count cells in a named range (Col."H" abt 300 out of 1000 lines containing
data) that are >= to columns "(C+H)+2". The format is "Currency" in both
columns. I'm sure there is a way, but........

Thanks
 
If I correctly understand what you are asking, try something like
=SUMPRODUCT(--(H1:H1000>=C1:C1000+H1:H1000+2))
or
=SUMPRODUCT(ISNUMBER(H1:H1000)*(H1:H1000>=C1:C1000+H1:H1000+2))

These array formulas use the fact that the logical values TRUE and FALSE can
be coerced into the numbers 1 and 0, allowing you to count the number of
times that the condition is true by simply summing. The first formula uses
-- to force the coercion, since logical values would normally be ignored by
sum functions. The second formula doesn't need it because the multiplication
(requires both conditions be met, i.e. AND) coerces the type conversion as a
by-product.

Note that the condition
H1:H1000>=C1:C1000+H1:H1000+2
can be simplified to
C1:C1000<=2

Jerry
 
Thanks Jerry for your answer. I tried both of them and the results are "zero".
Here is an example of what i am trying to do:

This is a church fund raising weekly lottery where:
C= Paid this week by this individual (entered value) Format: Currency
F= Calculated date of next payment due Format: Date
G= Last week's balance for this individual (Copied) Format: Currency
H= This week's balance after deducting $2 for the drawing (Calculated)
Format: Cur

C F G H
1 21 févr. 2010 $98,00 $96,00
2 $8,00 26 avr. 2009 $4,00 $10,00
3 1 nov. 2009 $66,00 $64,00
4 $2,00 29 mars 09 $0,00 $0,00
5 10 mai 2009 $16,00 $14,00
6 29 mars 09 $0,00 $0,00 $

I want to count the number of paid participants for "this week" which in
this example should be "5" (Line 6 did not pay for this week and had no
balance left from last week)

Thanks
 
If I now uderstand what you want, then almost every detail of your original
question was wrong, but either

=SUMPRODUCT(--(ROUND(H1:H1000,2)<=ROUND(C1:C1000+G1:G1000-2,2)))

or

=SUMPRODUCT(--(ROUND(H1:H1000,2)=ROUND(C1:C1000+G1:G1000-2,2)))

should work. Given that columns G and H are balances, it is not clear how
you would arrive at an inequality instead of an equality, hence the 2nd form.

The ROUND functions address the situation where values might not be integers
(computers can only approximate most decimal fractions, which can lead to
surprises if you try to do exact comparisons).

Jerry
 
Back
Top