Adding up values based oncertain criteria

[Howardc]

I would like a formula to add values which are in Col E based on account numbers which are in Col D for eg the account number series are 3099 to 3199 (these are four digit numbers) 3000D to 3199D (these are 5 digit numbers all ending with a D) 3000K to 3199K ,3000A to 3199A


I need a seperate formula for each of these account numbers


Your assistance in the regard is most appreciated

 

[Hui]

Howardc


Try something like:

=SUMPRODUCT((VALUE(LEFT(D3:D12,4))=3100)*(E3:E12))


or if you need the range:

=SUMPRODUCT((VALUE(LEFT(D3:D12,4))>=3100)*(VALUE(LEFT(D3:D12,4))<=3900)*(E3:E12))

 

[Howardc]

Hi Hui


Thanks for the reply, much appreciated