Results 1 to 5 of 5

Thread: EXCEL GURUS!!! Please see if you can help.

  1. #1
    Junior Member
    Join Date
    Jan 2008
    Location
    Cranebrook, NSW
    Posts
    140
    Thanks
    8
    Thanked 4 Times in 4 Posts
    Rep Power
    139
    Reputation
    90

    Default EXCEL GURUS!!! Please see if you can help.

    I have the formula below:

    Code:
    =(SUMIF(Feb!$C$3:$C$22,"*MSF*",Feb!$B$3:$B$22))+(SUMIF(Feb!$E$3:$E$22,"*MSF*",Feb!$D$3:$D$22))+(SUMIF(Feb!$G$3:$G$22,"*MSF*",Feb!$F$3:$F$22))
    The idea is that each column combo (C and B, E and D, G and F in this case) are figures and namecodes respectively. Each combo is a single day of the month. I need to add the entire month, however, the formula would be so long, it will become unmanageable. 3 days I can handle, 31 is stupid.

    Is there a more elegant way of doing this? I don't need to add up each month to make the year, it's just every day of each month.



Look Here ->
  • #2
    The Prince of Hardness
    Dr_Mohs's Avatar
    Join Date
    Nov 2014
    Location
    Cape Town, S. Afr.
    Posts
    1,357
    Thanks
    3,900
    Thanked 2,879 Times in 660 Posts
    Rep Power
    1246
    Reputation
    57570

    Default

    Will it still do what you want it to do if you just had it like this?

    =SUMIF(Feb!C3:C22,"*MSF*",Feb!B3:B22)

    Because if you then get the result in say cell C23 or B23, then you should just be able to copy/drag it over the other columns, for 31 cases.

  • The Following User Says Thank You to Dr_Mohs For This Useful Post:

    therufus (13-03-18)

  • #3
    Junior Member
    Join Date
    Jan 2008
    Location
    Cranebrook, NSW
    Posts
    140
    Thanks
    8
    Thanked 4 Times in 4 Posts
    Rep Power
    139
    Reputation
    90

    Default

    =SUMIF(Feb!C3:C22,"*MSF*",Feb!B3:B22) calculates the sum of all matches for "MSF" for that particular day. Pair C/B is an entire day for all figures. Similarly, there is =SUMIF(Feb!C3:C22,"*MSA*",Feb!B3:B22) which will return the sum of all "MSA". Column C/B is for the first day of the month. Columns E/D are for day 2, G/F is for day 3, and so on.

    Hence to add up for the entire month, I need to add up the summed returned values of all "MSA" matches, "MSB" matches, etc.

  • The Following User Says Thank You to therufus For This Useful Post:

    Dr_Mohs (13-03-18)

  • #4
    LSemmens
    lsemmens's Avatar
    Join Date
    Dec 2011
    Location
    Rural South OZ
    Posts
    7,338
    Thanks
    6,341
    Thanked 4,074 Times in 2,040 Posts
    Rep Power
    1783
    Reputation
    72740

    Default

    Put your SumIF formulae at the base each column If you need absolute references, I'm presuming you know how to do that. Place your "MSF" formulae on one row and your "MSA"s on the next. Then all you need to use a SUM formula to total each row.
    I'm out of my mind, but feel free to leave a message...

  • The Following 2 Users Say Thank You to lsemmens For This Useful Post:

    Dr_Mohs (13-03-18),therufus (13-03-18)

  • #5
    Junior Member
    Join Date
    Jan 2008
    Location
    Cranebrook, NSW
    Posts
    140
    Thanks
    8
    Thanked 4 Times in 4 Posts
    Rep Power
    139
    Reputation
    90

    Default

    Thanks guys. I decided to take your advice and compartmentalise the calculations in the monthly sheets, then add in the year total sheet.

  • The Following User Says Thank You to therufus For This Useful Post:

    Dr_Mohs (13-03-18)

  • Bookmarks

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •