Formula HELP!!

[jpkreider]

Here is my current formula for G18:

=IF(ISNUMBER(E18),VLOOKUP(E18,ReferenceTables!$B$13:$C$26,2)-F18,"0")

I'd like to adjust it to say that if H18, I18 or J18 has any text at all, that G18 is reduced by 1 for each cell that has text.

Is that possible to add to this formula? Thanks for your help!!

 

[PatOBrien198]

try adding this to the end of your formula:

- CountA(H18:J18)
(minus the count of cells in the range that have something in it.)

 

[Scott T]

Try

=IF(ISNUMBER(E18),VLOOKUP(E18,ReferenceTables!$B$13:$C$26,2)-F18,"0")-SUMPRODUCT(--ISTEXT(H18:J18))

 

[etaf]

if you only want text and not numbers
try

=SUM(COUNTIF(INDIRECT({"H18","I18","J18"}),"*"))

=IF(ISNUMBER(E18),(VLOOKUP(E18,ReferenceTables!$B$13:$C$26,2)-F18)-(SUM(COUNTIF(INDIRECT({"H18","I18","J18"}),"*"))),"0")

i thought non contiguous range opps

=IF(ISNUMBER(E18),(VLOOKUP(E18,ReferenceTables!$B$13:$C$26,2)-F18)-(COUNTIF(H18:J18,"*")),"0")