I have a formula but need something else from it

[richard hales]

Good evening everyone

I have this formula which works great but I want it to do something else
=IF(COUNTA(B4:B7),SUMPRODUCT(SUMIF('cmart code'!D2:D15,B4:B7,'cmart code'!E2:E15)),"")
Sheet1. B4:B7 is where the formula looks for a code
Sheet2. 'cmart code'!D2:D15 is the lookup code in this case it’s D2=service1 to D15= service14.
'cmart code'!E2:E15)),"") E2=1 E15=14 and if nothing is entered in B4:B7 it returns a blank cell, in Sheet2. Cell B15.
Now if any one can help me please what I need it to do now is:
D2=service1 to D15= service14 to stay the same but E2=1 E15=14 to change to GF

 

[nbrcrunch]

from the way you've explained it, what you need to do is merely edit the E-column Range to have the alpha characters "GF" in them.

 

[richard hales]

thanks for the reply nbrcrunch, i changed the E-column to GF but in Cell B15 it returns a 0 am looking for it to return GF or stay blank.

 

[Aladin Akyurek]

The objctive is not very clear...

If you want to copy the formula across with the sum range changing...

=IF(COUNTA($B$4:$B$7),SUMPRODUCT(SUMIF('cmart code'!$D$2:$D$15,$B$4:$B$7,'cmart code'!E$2:E$15)),"")

If you want it to have GF2:GF15 as the sum range, use an adjusted copy of the formula:

=IF(COUNTA(B4:B7),SUMPRODUCT(SUMIF('cmart code'!D2:D15,B4:B7,'cmart code'!GF2:GF15)),"")

If the foregoing is of no help, try to elaborate on your objective.