Ignoring Formulas within cells

J

jimswinder

Is there a way to have a function/formula when evalutating a cell, look at
the result of the formula within that cell and not the formula? For instance:
The formulas in range A1:A20 is =IF(LEFT($G4,3)="DET",G4,""). How can I
count/sum the instances in range A1:A20 where =IF(LEFT($G4,3)="DET",G4,"")
is true?

I have tried:
COUNTif(LEFT($A1:A20,3)="DET")
SUMIF(LEFT($A1:A20,3)="DET").

Thanks...
 
J

jimswinder

But there is more than just DET in the result....it could be DET Jones, DET
Smith, etc....
 
D

Duke Carey

Sorry - didn't read that too closely

How about

=COUNTA($A1:A20)-COUNTIF($A1:A20="")
 
J

jimswinder

I will try that...but won't it recognize the formula in the cell and think it
is not blank ("")?
 
J

jimswinder

It says I have entered too few arguements...

jimswinder said:
I will try that...but won't it recognize the formula in the cell and think it
is not blank ("")?
Click to expand...
 
B

Bondi

jimswinder said:
I will try that...but won't it recognize the formula in the cell and think it
is not blank ("")?
Click to expand...

Hi,

Maybe you can try a SUMPRODUCT approach. Something along the lines of:

=SUMPRODUCT(--(LEFT(A1:A20,3)="det"))

Regards,
Bondi
 
J

jimswinder

do I need to input a range where the -- is in the fromula
=SUMPRODUCT(--(LEFT(A1:A20,3)="det"))
 
J

jimswinder

THAT WORKED!!!!!

THANKS!!!!

Bondi said:
Hi,

Maybe you can try a SUMPRODUCT approach. Something along the lines of:

=SUMPRODUCT(--(LEFT(A1:A20,3)="det"))

Regards,
Bondi
Click to expand...
 
Top