+ Reply to Thread
Results 1 to 7 of 7

Sum values based on TRUE/FALSE property on different sheet?

  1. #1
    Registered User
    Join Date
    05-15-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Sum values based on TRUE/FALSE property on different sheet?

    Hello,

    I think VBA is what I need to solve this problem but want to see if I’m going down the right path before I start as I don’t know VBA at all and will have to start reading some books.

    The sheet “August Lint Results” records how many pieces of lint were found on the floor of that dealership on that day.

    The sheet “Cleaners Activity” indicates if the cleaners cleaned that dealership that morning. (TRUE meaning they did clean the dealership, FALSE otherwise)

    I need to determine if the cleaners are doing a good job by creating an average pieces of lint with cleaners and an average without. So in short I want to count the pieces of lint on TRUE days and the pieces of lint on FALSE days separately and report on the overall average. This will allow us to determine if we should keep using the cleaners.

    I’ve asked some Excel gurus at work and nobody knows how to do this. I’m thinking this is a job for VBA but as I said earlier just want to see if I’m on the right path or is there a more obvious solution?

    Thanks!

    Grant
    Attached Files Attached Files

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Sum values based on TRUE/FALSE property on different sheet?

    Hello Grant.

    Fire your "Excel Gurus". They don't deserve the title.

    You don't need VBA. All you need is a helper column on the "August Lint Results" sheet. You can hide this column if you want. The formula in this column is

    =INDEX('Cleaners Activity'!$B$2:$F$4,MATCH('August Lint Results'!A2,'Cleaners Activity'!$A$2:$A$4,0),MATCH('August Lint Results'!B2,'Cleaners Activity'!$B$1:$F$1,0))

    It will tell on a row by row basis, if a store has been cleaned on that particular date.

    Then you can use the variety of Sumifs(), Countifs() and Averageifs() on the "Desired Results" sheet.

    Someone here will probably come up with a one cell solution that does not require a helper formula, but the general opinion among knowledgeable Excel users is that helper columns are often more helpful for the creation of formulas that are fast, which is beneficial for large datasets.

    see the attached file for the formulas.

    cheers,
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    05-15-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Sum values based on TRUE/FALSE property on different sheet?

    Thanks so much teylyn! That was really helpful, you are a very skilled Excel programmer!

    One more related question for which I might be in the wrong forum (tell me if I am) but the dealer codes in that example aren't actually on every row. In the attached file you can see that the data I get from the reporting tool actually generates only the first dealer code for the date sequence. To get your solution to work I need to figure out how to get the dealer code put into every row (and there are thousands of rows). Is that VBA or is there again some sort of formula?

    Thanks again!

    Grant

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Sum values based on TRUE/FALSE property on different sheet?

    Hello Grant,

    select column A from A1 down to the last row with data in column B (in your attached example it would be A1:A16.
    Hit F5 and click the "Special" button
    Tick "Blanks" and hit OK
    type a = sign and hit the up arrow
    hold down the Ctrl key and hit Enter.

    Now all cells are filled with the dealer codes from above. If you want, you can now copy the whole column A and use Paste Special - Values to paste the actual code values over the formulas.

    cheers,

  5. #5
    Registered User
    Join Date
    05-15-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Sum values based on TRUE/FALSE property on different sheet?

    Hey teytyn,

    Thanks so much for all your help! If you're ever in Toronto drop me a line as I owe you a beer or two. Without your help I'd still be reading some VBA book.

    One more thing, the reporting tool at work that makes all this data apparently likes to skip days if you have the dealer codes on rows, so I've been working today on switching it all so that it works with the dealer codes on top. I've got everything switched around and the formulas rewritten and they work fine (Thanks to your help!). The only problem I'm having is that on the "Desired Results" sheet I can't figure out how to autofill the formulas over from column to column. The formula's are trying to increment the indexes by +1 but because of the "Helper row" they need to go up by +2. So I can't drag the formulas along the row. I suppose I could do it manually but I've got years worth of data to work with so eventually doing that manually is going to get old.

    I've shown on the "Desired Results" tab what I'm trying to explain.

    Actually come to think of it the INDEX formula on the "August Lint Results" seems to want to do the same thing. When I try to paste it in it references the row before it, not two before it like I would like it to.

    Thanks so much!!

    Grant
    Attached Files Attached Files

  6. #6
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Sum values based on TRUE/FALSE property on different sheet?

    Grant,

    you've managed to mangle the solution that would look at all the data, and instead created a formula that looks at only one row. Now you're struggling to apply that formula accros columns, and want to skip every other row.

    Classic case of bad spreadsheet design.

    Now, do yourself a favour and create a simple data entry table, with one row per date per store and enter the True/False into the same sheet.

    See the attached file with the new Data Entry sheet and the new formulas in the shaded cells. If you want consistent formulas, you need consistent data.

    I won't support inconsistent spreadsheet design. It's a PITA and not worth the effort.

    cheers
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-15-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Sum values based on TRUE/FALSE property on different sheet?

    Thanks for your help teylyn! I've been able to use your suggestions and things seem to be working!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1