IF and mid function to sum a range

  • Kindly advice me on the following:-


    1. Sheet(“E”) has 2 columns.
    2. Column A in Sheet(“E”) is a list of Thousand of Account codes in text format,i.e.,123/44/99A8, 123/44/00B1,234/87/A9 and etc.
    3. Column B in Sheet(“E”)is Amount in number.
    4. Sheet(“R”) is in a report format.
    5. Please advice me on the following using if function and VBA CODES.
    5.1 Sum Range(“B:B”) in Sheet(“E”) to Range(“C10”) in Sheet(“R”) , if the Mid character in Column A in Sheet(“E”) is “A”.
    5.2 Sum Range(“B:B”) in Sheet(“E”) to Range(“C12”) in Sheet(“R”) , if the Mid character in Column A in Sheet(“E”) is “B”.
    6. I would like to place the VBA Codes in the VBProject of Sheet(“R”).


    Thank you. :)
    Respectfully yours
    Susan

  • Re: IF and mid function to sum a range


    Hi Susan,


    You don't need to use VBA to extract the total of values relating to your text entries containing A or B. If you place the following formula in any cell of sheet R, it will give you the total where A is found in column A:


    =SUMPRODUCT((ISERROR(FIND("A",UPPER($A1:$A12)))=FALSE)*($B1:B12))


    You will need to change the end cell numbers to reflect the length of your data.


    Hope this helps.


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: IF and mid function to sum a range


    Dear Batman,
    I appreciate you for your precious times and efforts to provide me with the formula and function.


    I regret to inform you that the end result is 0 appeared in the cell.


    I think this might be because there are blank rows in the sheet.


    Please advice.


    Thanking you.
    Respectfully yours,
    Susan

  • Re: IF and mid function to sum a range


    Dear Batman and Pradeep atm,


    I thank both of you very much for providing me with such an excellence formula: Sumproduct respectively.


    Both of the formulas worked fine within certain range.


    Unfortunately, inside the whole of Column A & C, there are about 1000 number formatted as Text. Therefore, #VALUE appeared in the Sheet(“R”).


    Is there any other way for the sumproduct formula to exclude those Text in the Column C & A?



    Kindly advice.
    Respectfully yours,
    Susan
    :thanx:

  • Re: IF and mid function to sum a range


    Susan,


    When you refer to columns A & C, do you now have your values, that were previously referred to as being in column B, in column C?


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: IF and mid function to sum a range


    Dear Batman,


    I thank you for your speedy respond to my problem.


    Please accept my apoloy for the wrong column that I have mentioned to you.


    When I refer to your quote as follow:-


    When you refer to columns A & C, do you now have your values, that were previously referred to as being in column B, in column C?


    Sorry for the inconvenience caused.


    Regards,
    Batman.[/QUOTE]


    I was referring to Columns A & B, and not Columns A & C.


    There are text in some of the rows inside the column of B, that is why I think the sumproduct formula cannot do the summing. The result came out as #VALUE.


    Please advice me.


    Respectfully yours,
    Susan

  • Re: IF and mid function to sum a range-SOLVED


    Dear Batman and Pradeep ATM’s


    Yeepee! :dance:


    I got it! I got it!


    I thank you both of yours sumproduct formula.


    To tell the truth, I have been doing the stupid summing since the year 2004, and also I noticed that the row numbers in the column A kept on changing or increasing year by year.


    Can you imagine that I have to do the summing the amount for over 6000 account codes frequently?:crying:


    Both of you guys are really nice, considerate and good people to me.


    I appreciate you guys, and finally many thanks to both of you guys' times and efforts. :cheers:


    Respectfully yours,
    Susan


    Obviously everyone wants to be successful, but
    I want to be looked back on as being very
    innovative, very trusted and ethical and ultimately
    making a big difference in the world.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!