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
    143
    Thanks
    8
    Thanked 6 Times in 6 Posts
    Rep Power
    204
    Reputation
    130

    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
    Senior Member
    irritant's Avatar
    Join Date
    Nov 2014
    Posts
    1,684
    Thanks
    5,055
    Thanked 3,664 Times in 831 Posts
    Rep Power
    1636
    Reputation
    73270

    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 irritant For This Useful Post:

    therufus (13-03-18)

  • #3
    Junior Member
    Join Date
    Jan 2008
    Location
    Cranebrook, NSW
    Posts
    143
    Thanks
    8
    Thanked 6 Times in 6 Posts
    Rep Power
    204
    Reputation
    130

    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:

    irritant (13-03-18)

  • #4
    LSemmens
    lsemmens's Avatar
    Join Date
    Dec 2011
    Location
    Rural South OZ
    Posts
    10,585
    Thanks
    11,867
    Thanked 7,061 Times in 3,338 Posts
    Rep Power
    3153
    Reputation
    132592

    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:

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

  • #5
    Junior Member
    Join Date
    Jan 2008
    Location
    Cranebrook, NSW
    Posts
    143
    Thanks
    8
    Thanked 6 Times in 6 Posts
    Rep Power
    204
    Reputation
    130

    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:

    irritant (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
    •