subtract a single cell from range of cells and then count

[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.

 

[winnie123]

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

 

[Jacob Skaria]

Check your previous post

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

If this post helps click Yes

 

[T. Valko]

Try this:

=SUMPRODUCT(--(E2:E10-A2>30))

 

[winnie123]

ps

Alter the range to suit

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

 

[tworrall]

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]

'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

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.

Check your previous post

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

If this post helps click Yes

 

[tworrall]

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.

'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

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.

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.

 

[tworrall]

This help as now I understand how SUMPRODUCT can work differently than what I
thought. I still don't understand all of it, but this might help others
http://www.xldynamic.com/source/xld.SUMPRODUCT.html