subtract a single cell from range of cells and then count

  • Thread starter Thread starter tworrall
  • Start date Start date
T

tworrall

I want to subtrcat a single cell from each value in a range. For each result,
if greater tthan 30, I want to count those instances.

In other words, I would subtract A2 from E:E (each cell). I want to count
each time the result is greater than 30.
 
Hi,

you can copy value in A2 and then highlight the range in column E, paste
special then click on subtract.

To get the count use formula in F2 or where desired

=COUNTIF(E2:E8,">30")
 
Check your previous post

=SUMPRODUCT((A2-E1:E100>30)*(E1:E100>0))

If this post helps click Yes
 
ps

Alter the range to suit

winnie123 said:
Hi,

you can copy value in A2 and then highlight the range in column E, paste
special then click on subtract.

To get the count use formula in F2 or where desired

=COUNTIF(E2:E8,">30")
Click to expand...
 
This is gettign me 1/2 the way there. Hereis the logic:
I have a date in A2
I have a range of dates in range E:E
If I subtract A2 from E I get a value. If that value is greater than 30 I
count 1. So if there are 40 cells where A2-E:E > 30 I return 40. I think your
formula works but I have more to consider.

I must also ask if colums A:A equals "no" or "" [blank]

So I am now only counting if the value is >30, and A:A is no or blank.
In the original logic I said I would get a count of 40. If there are 7
blanks or Nos, then the return value would be 33.
 
'I am a bit confused...You are deducting ColE values from cell A2. at the
same time you are checking for = ""...If that is correct you can simply add
one more condition

'ColA is blank
=SUMPRODUCT((A2-E1:E100>30)*(E1:E100>0)*(A1:A100=""))

'ColA is blank or no
=SUMPRODUCT((A2-E1:E100>30)*(E1:E100>0)*(A1:A100={"","no"}))

If this post helps click Yes
---------------
Jacob Skaria


tworrall said:
This is gettign me 1/2 the way there. Hereis the logic:
I have a date in A2
I have a range of dates in range E:E
If I subtract A2 from E I get a value. If that value is greater than 30 I
count 1. So if there are 40 cells where A2-E:E > 30 I return 40. I think your
formula works but I have more to consider.

I must also ask if colums A:A equals "no" or "" [blank]

So I am now only counting if the value is >30, and A:A is no or blank.
In the original logic I said I would get a count of 40. If there are 7
blanks or Nos, then the return value would be 33.


Jacob Skaria said:
Check your previous post

=SUMPRODUCT((A2-E1:E100>30)*(E1:E100>0))

If this post helps click Yes
Click to expand...
Click to expand...
 
Sorry- I see why I confused you. I am looking at a totally differnt column
for the blank or "no". I have to reference that column and exclude that
record from the count if it has a blank or "No"

I'll tinker with the formula you gave me but any further advise would be
helpful.

Jacob Skaria said:
'I am a bit confused...You are deducting ColE values from cell A2. at the
same time you are checking for = ""...If that is correct you can simply add
one more condition

'ColA is blank
=SUMPRODUCT((A2-E1:E100>30)*(E1:E100>0)*(A1:A100=""))

'ColA is blank or no
=SUMPRODUCT((A2-E1:E100>30)*(E1:E100>0)*(A1:A100={"","no"}))

If this post helps click Yes
---------------
Jacob Skaria


tworrall said:
This is gettign me 1/2 the way there. Hereis the logic:
I have a date in A2
I have a range of dates in range E:E
If I subtract A2 from E I get a value. If that value is greater than 30 I
count 1. So if there are 40 cells where A2-E:E > 30 I return 40. I think your
formula works but I have more to consider.

I must also ask if colums A:A equals "no" or "" [blank]

So I am now only counting if the value is >30, and A:A is no or blank.
In the original logic I said I would get a count of 40. If there are 7
blanks or Nos, then the return value would be 33.


Jacob Skaria said:
Check your previous post

=SUMPRODUCT((A2-E1:E100>30)*(E1:E100>0))

If this post helps click Yes
---------------
Jacob Skaria


:

I want to subtrcat a single cell from each value in a range. For each result,
if greater tthan 30, I want to count those instances.

In other words, I would subtract A2 from E:E (each cell). I want to count
each time the result is greater than 30.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top