Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas only calculate a result when there is matching data in ce
I have the following formulas setup gratefully received from microsoft online
members. These work perfect for what I need them for =DATE(YEAR($C2),MONTH($C2)+3,DAY($C2)-1) =SUMPRODUCT(($B3:$B7<D2)*($C3:$C7<D$1)) What I need is to add the above formulas inside other formulas so that they only calculate where cells are the same. I only want to enter this formula once at the top of the sheet and be able to copy it down the sheet. Example If cells A2= 1 the above formulas will calculate because cells A2:A4 = 1 A3= 1 A4= 1 A5= 2 no calculations (blank cells) will be used as there is only one entry of 2 A6= 3 the above formulas will calculate because cells A6:A10 = 3 A7= 3 A8= 3 A9= 3 A10=3 etc... Any help appreciated |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas only calculate a result when there is matching data in ce
Hi Mally,
You might add the following: =IF(COUNTIF(A$1:A$1000,A1)1,yourformula,"") Cheers, Shane Devenshire Microsoft Excel MVP "Mally" wrote in message ... I have the following formulas setup gratefully received from microsoft online members. These work perfect for what I need them for =DATE(YEAR($C2),MONTH($C2)+3,DAY($C2)-1) =SUMPRODUCT(($B3:$B7<D2)*($C3:$C7<D$1)) What I need is to add the above formulas inside other formulas so that they only calculate where cells are the same. I only want to enter this formula once at the top of the sheet and be able to copy it down the sheet. Example If cells A2= 1 the above formulas will calculate because cells A2:A4 = 1 A3= 1 A4= 1 A5= 2 no calculations (blank cells) will be used as there is only one entry of 2 A6= 3 the above formulas will calculate because cells A6:A10 = 3 A7= 3 A8= 3 A9= 3 A10=3 etc... Any help appreciated |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas only calculate a result when there is matching data i
Thanks Shane
When I add your formula i need my formula to change e.g. if cells A1:A10 all contain the same number then =IF(COUNTIF(A$1:A$1000,A1)1,SUMPRODUCT(($B3:$B10< D2)*($C3:$C10<D$1)),"") It's hard to explain without showing you my file. Thanks for your help "Shane Devenshire" wrote: Hi Mally, You might add the following: =IF(COUNTIF(A$1:A$1000,A1)1,yourformula,"") Cheers, Shane Devenshire Microsoft Excel MVP "Mally" wrote in message ... I have the following formulas setup gratefully received from microsoft online members. These work perfect for what I need them for =DATE(YEAR($C2),MONTH($C2)+3,DAY($C2)-1) =SUMPRODUCT(($B3:$B7<D2)*($C3:$C7<D$1)) What I need is to add the above formulas inside other formulas so that they only calculate where cells are the same. I only want to enter this formula once at the top of the sheet and be able to copy it down the sheet. Example If cells A2= 1 the above formulas will calculate because cells A2:A4 = 1 A3= 1 A4= 1 A5= 2 no calculations (blank cells) will be used as there is only one entry of 2 A6= 3 the above formulas will calculate because cells A6:A10 = 3 A7= 3 A8= 3 A9= 3 A10=3 etc... Any help appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas to calculate only when data is populated. | Excel Worksheet Functions | |||
Auto calculate formulas when new data is entered | Excel Discussion (Misc queries) | |||
How do I calculate formulas for yes/no data | Excel Worksheet Functions | |||
Count or result of matching cells | Excel Worksheet Functions | |||
Will not calculate average/median formulas;acts like no data in c. | Excel Worksheet Functions |