• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 799
  • Last Modified:

Conditional sum in excel 2010

Dear Sir
im trying to sum values depending on Conditional  vlaue

code  code2     date      amount
C      N2      11/01/2012      4860
C      N1      29/01/2012      4850
C      N4      20/02/2012      5010
C      01      01/03/2012      1210
C      01      01/03/2012      1210
D      F6      11/01/2012      -20
D      01      14/01/2012      -1210
D      01      14/01/2012      -1210
D      01      14/01/2012      -1210
D      01      14/01/2012      -1210
D      01      29/01/2012      -1210
D      01      29/01/2012      -1210
D      01      29/01/2012      -1210

 wht im trying to do is to sum only vlues that in the column amount that dosen't have alphabetical character in column code2
0
osama120
Asked:
osama120
  • 2
  • 2
2 Solutions
 
barry houdiniCommented:
Try this

=SUMPRODUCT(ISNUMBER(B2:B100+0)+0,D2:D100)

where B2:B100 contains codes and D2:D100 the amounts, adjust ranges as required

regards, barry
0
 
Elton PascuaCommented:
Barry is the master of formulas. :)

I was just wondering what's the purpose of +0 at the end of the range?
0
 
barry houdiniCommented:
Thanks, techfanatic

The first +0 is to deal with text formatted numbers, e.g. if B2 contains 01 formatted as text then ISNUMBER(B2) returns FALSE but I think 01 should be recognised as a number here, even if text formatted, so ISNUMBER(B2+0) should return TRUE as required

regards, barry
0
 
Elton PascuaCommented:
Thanks for explaining Barry.
0
 
osama120Author Commented:
thanks
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now