Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
subtract one cell from each cell in a range and count instances | Excel Worksheet Functions | |||
COUNT IF A VALUE FALLS WITHIN A RANGE IN A SINGLE CELL | Excel Discussion (Misc queries) | |||
Would like one cell to both count and multiply a range of cells | Excel Worksheet Functions | |||
How to I copy text from a range of cells to another single cell? | Excel Discussion (Misc queries) | |||
How to count a range of values in a single cell? | Excel Worksheet Functions |