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

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

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