subtract a single cell from range of cells and then count
 

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.

subtract a single cell from range of cells and then count
 

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" wrote:

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.

subtract a single cell from range of cells and then count
 

Check your previous post

=SUMPRODUCT((A2-E1:E10030)*(E1:E1000))

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


"tworrall" wrote:

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.

subtract a single cell from range of cells and then count
 

Try this:

=SUMPRODUCT(--(E2:E10-A230))

--
Biff
Microsoft Excel MVP


"tworrall" wrote in message
...
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.

subtract a single cell from range of cells and then count
 

ps

Alter the range to suit

"winnie123" wrote:

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" wrote:

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.

subtract a single cell from range of cells and then count
 

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" wrote:

Check your previous post

=SUMPRODUCT((A2-E1:E10030)*(E1:E1000))

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


"tworrall" wrote:

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.

subtract a single cell from range of cells and then count
 

'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:E10030)*(E1:E1000)*(A1:A100=""))

'ColA is blank or no
=SUMPRODUCT((A2-E1:E10030)*(E1:E1000)*(A1:A100={"","no"}))

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


"tworrall" wrote:


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" wrote:

Check your previous post

=SUMPRODUCT((A2-E1:E10030)*(E1:E1000))

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


"tworrall" wrote:

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.

subtract a single cell from range of cells and then count
 

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" wrote:

'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:E10030)*(E1:E1000)*(A1:A100=""))

'ColA is blank or no
=SUMPRODUCT((A2-E1:E10030)*(E1:E1000)*(A1:A100={"","no"}))

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


"tworrall" wrote:


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" wrote:

Check your previous post

=SUMPRODUCT((A2-E1:E10030)*(E1:E1000))

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


"tworrall" wrote:

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.

subtract a single cell from range of cells and then count
 

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


"tworrall" wrote:

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.