• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

sum if function - help required

V

vpxlquest

Member
Can anyone help me with this


i have to sum data in excel


A040 - 4

G999 - 7

C987 - 2

D998 - 4

A140 - 6

G299 - 2


I have tried the sumif funciton by adding another helper column with only the alphabets using this formula


=SUMIF(F26:H38,"A",G26:G38)

i want to sum all the alphabets together by seperating the alphanumeric characters is there any other way. i am not very good with coding so i do require some help in the right direction.
 
Either a helper column with formula:

=LEFT(A2,1)

and then run the SUMIF off of that, or use the single formula:

=SUMPRODUCT(--(LEFT(F26:F38,1)="A"),(G26:G38))
 
USING THE LEFT (A2,1) AND THEN USING SUM IF WORKED


=SUMIF(E26:G38,"A",G26:G38)


I AM GETTING 0 WITH SUMPRODUCT I WOULD PREFER USING A SINGLE FORMULA WHAT AM I DOING WRONG.

thanks for all you help
 
Hello luke,


=SUMPRODUCT(--(LEFT(F26:F38,1)="A"),(G26:G38))


i had omitted the -- in the formula the first time. is this data for the first array. i have got it working thanks again for all your help. now it works with one forumula
 
I would like to go a step further and use a button were a sheet 2 is opened and sumproduct of sheet1 gets calculated when the update button is pressed.


=SUMPRODUCT(--(LEFT($C$12:$C$1000,1)="A"),($D$12:$D$1000))

=SUMPRODUCT(--(LEFT($C$12:$C$1000,1)="B"),($D$12:$D$1000))


etc. i would like to export only the sheet 2 calculated values over to a database or for my calculation.is this possible. the key feild like project name and details along with the calcualted values should be transferred to sheet 2. any help would be greatly appreciated.
 
I tried this macro to copy from my worksheet into a worksheet call ANALYZE.

Macro

Range("P21:p26").Select

Selection.Copy

Sheets("ANALYZE").Select

ActiveSheet.Paste

End Sub


i have this macro which selects the cells P21:p26. Since this is a calculated cells with forumlas it is copying only 0 how to copy formulated cells on to another worksheet indicating the destination range.
 
I wasn't sure where you wanted to paste the data into on sheet ANALYZE, but you want to do a paste special - values only

[pre]
Code: 
Sub CopyValues()

Range("P21:P26").Copy
Sheets("ANALYZE").Range("P21:P26").PasteSpecial Paste:=xlPasteValues
End Sub
[/pre]
 
You must log in or register to reply here.
Back
Top