Nesting formulas

[Hatle]

Hi,

I have a query on nesting formulas.

I am trying the folling argument.

I have code that I enter in any Cell for example 'BG123'

I want to sumif all the values for BG123 in the source
table. Only if the General Ledger code is equal or greater
than 4100.

=IF(B:B>4100,SUMIF(A:A,E3,C:C),0)

If anyone can help that would be great.

Thanks

Hatle

 

[Aladin Akyurek]

=SUMPRODUCT(--($A$2:$A$100=E3),--($B$2:$B$100>4100),$C$2:$C$100)

Formulas operating on arrays like the foregoing do not admit whole columns
such as A:A as reference.

 

[Hatle]

Hi,

That was great.

However some of the source data has gaps within the cells
and therefoe not returning the values in to the cell.

For Example Data source table on project number would be
like this:

BG100
BG100
BG100
BG100...
BG100...

Thus the formula is returning values where BG100 has no
spaces.

Is there a solution where I can format all the cells so
the values are picked up.

Thanks

H

 

[Aladin Akyurek]

Not sure about the problem... Maybe:

=SUMPRODUCT(--(TRIM($A$2:$A$100)=TRIM(E3)),--($B$2:$B$100>4100),$C$2:$C$100)

If this is way off, try to elaborate on the issue.