+ Reply to Thread
Results 1 to 26 of 26

sumproduct and/or index/match(?)

  1. #1
    Registered User
    Join Date
    11-23-2013
    Location
    Washington DC
    MS-Off Ver
    Excel 2010
    Posts
    69

    sumproduct and/or index/match(?)

    Greetings everyone!

    I have a somewhat convoluted counting situation in which I normally use SUMPRODUCT (thanks, Excel Forum, for the education!), but in this case I maybe need to do an INDEX/MATCH-type function across sheets. It could be an array.

    Specifically, I need to count the number of a particular grade (A-E) that's in C:C on the Evaluations sheet (whole sheet included). There's also a date in D:D on that sheet.

    Then what I need to do is reference the report serial (B:B) on the Evaluations sheet and match it with the serial (B:B) on the Reports sheet. Then I need the formula to look at the Department in A:A on the Reports sheet.

    Finally, I need a count by grade (B:B through F:F) by department (in A3:A10) by month (A1) on the Departments sheet (that's the report I need to run from the data on the Reports and Evaluations sheets).

    Hopefully that makes sense. TL;DR: count grades by Department by month across two sheets, and placing a report on a third.

    I greatly appreciate everyone's assistance. Thanks again!
    Attached Files Attached Files

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: sumproduct and/or index/match(?)

    Then what I need to do is reference the report serial (B:B) on the Evaluations sheet and match it with the serial (B:B) on the Reports sheet. Then I need the formula to look at the Department in A:A on the Reports sheet.
    These serial numbers aren't unique, so this step is going to be really tough.
    And the dates don't match.

    Basically I'm not seeing any correlation between the data in the "Reports" tab and the "Evaluation" tab.

    You've got multiple records per cell in the Evaluations!B2:B7 range.

    Also,
    Departments!A1 isn't a date, it's the "Department" header on the table; and the range is A2:A7, not A3:A10. I presume that you deleted a row or something?

    My gut feeling is,
    If you can get the "Reports" and "Evaluations" relationship explained, (or ideally merged into one table!), then it won't be hard to get the desired results through COUNTIFS.

  3. #3
    Registered User
    Join Date
    11-23-2013
    Location
    Washington DC
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: sumproduct and/or index/match(?)

    Excellent questions.

    Briefly, I pull the sheet Reports, which includes which Department issued which report (via serial) and on what date.

    Evaluators evaluate those reports and I get that via the Evaluations sheet. The evaluators may evaluate one or more reports, again, on a certain date.

    As an aside, the date in Departments A1 could go anywhere (I plan on doing an INDIRECT to switch from month to month, with the Departments layout looking the same every time). The month does not have to appear on the sheet per se.

    When I compile the Departments report, I need to get the grade spread by month (Departments B through F), meaning that if Department 201 issued a report in Mar 2013, and an evaluation came in May 2013 (Evaluations D:D), I need to count how many grades (Evaluations C:C) of a certain letter (A-E) that department got for Mar 2013 and so on. Accordingly, an evaluation may come in six months later for a Mar 2013 report, and I need to count it against March's numbers in the Departments sheet. Finally, the serial in Evaluations may be referenced more than once per evaluator/month, so it would need to be counted each time, in that case, like COUNTIFS.

    Was that a better explanation?

    It may very well be COUNTIFS, but I guess I can't figure out how to match the serial B:B in Evaluations with serial B:B in Reports, then reference Department in A:A Reports to count for the Departments sheet by Department, by grade, by month. It's potential multiple values in Evaluations B:B that is confounding me.

    Merging the tables would be cumbersome due to multiple values in a cell in both tables. I know the preferable answer is a database, but my organization is Excel-centric.

    Tks again for the help!
    Last edited by jimbosi; 06-26-2014 at 09:15 AM. Reason: comment re: merging sheets

  4. #4
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: sumproduct and/or index/match(?)

    So it's dated based on the Reports tab; we're basically ignoring the date in the Evaluations tab for the purposes of this sheet.

    Quote Originally Posted by jimbosi View Post
    Evaluators evaluate those reports and I get that via the Evaluations sheet. The evaluators may evaluate one or more reports, again, on a certain date.
    This sounds like there's something going wrong.

    So you have a Serial, and they get evaluated by a Rater; the Rater takes a Serial, and then provides a "Grade" and the "Date" they graded it as outputs, and stamp it with their name.

    If that is so, then you should definately be splitting each Serial number and sticking it in it's own row. Like, if that's what you're getting as an output, then you should probably change that sheet too.

    As it stands, it might be possible to get where you need to go with SEARCH or FIND, but it's going to be ugly as sin if it even is possible.

    How do you feel about sticking helper columns in the Report and Evaluation tabs?

  5. #5
    Registered User
    Join Date
    11-23-2013
    Location
    Washington DC
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: sumproduct and/or index/match(?)

    Yes, the dates in the Evaluations sheet aren't part of the equation.

    I'm fine with helper columns, but splitting the data would be arduous. I do a bunch of other reports that depend on keeping everything together. The Reports sheet contains about 27000 rows with many more columns of data, while the Evaluations sheet is about 15000 rows, again, with other data.

    I can sort of envision a partial extraction of just the serial and grade in the Evaluations sheet, but I'm not sure how I'd break out the serials and repeat the grade in each adjacent cell.

    So all things considered, I'd prefer to keep the integrity of the sheets.

    As an aside, I'm aware a DB is preferable, but that's a whole other obstacle.

    Thanks again for the feedback. I look forward to your response. I appreciate you spending time with my problem.

    (Multiple values in a cell are the bane of my existence!)

  6. #6
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: sumproduct and/or index/match(?)

    I can't figure out how to do this without putting each Serial # on its own row first.

    It's what you have to use to index everything else, so each Serial in Evaluations needs its own row. I don't see any way around that.

  7. #7
    Registered User
    Join Date
    11-23-2013
    Location
    Washington DC
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: sumproduct and/or index/match(?)

    Maybe I could make that work. I could strip out the serial, Text to Columns, do an INDEX/MATCH on grades in an adjacent column, then . . . a SUMPRODUCT maybe?
    Last edited by jimbosi; 06-26-2014 at 02:29 PM. Reason: command change

  8. #8
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: sumproduct and/or index/match(?)

    I think I have something that works, take a look. Feeling rather dizzy after this...
    Attached Files Attached Files
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  9. #9
    Registered User
    Join Date
    11-23-2013
    Location
    Washington DC
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: sumproduct and/or index/match(?)

    Wow! So you did helper columns with an array in Reports that totaled the grades regardless of month, then just a SUMPRODUCT in Departments. Clever! I'm bad at helper columns, but I see that you did the reverse: established a search from the Report serial into the Evaluations serial. That's definitely an alternate way I envisioned it, and it looks like it works as I described it. Thank you very much!

  10. #10
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: sumproduct and/or index/match(?)

    Glad to hear it works!
    For the record, on Reports there are array formulas, have to be entered with Ctrl + Shift + Enter but you knew that.

    Function wise, I guess you could say that the formula in the Reports sheet "unfolds" the data in Evaluation to a more standard form and then Departments create a report from that the normal way.

    Despite helper columns I am pleased with how this turned out, it looks clean and the formulas are fairly readable.
    I was pretty close to creating a "single cell" solution but that would be just for the heck of it, nothing else. Personally I think helper columns are underrated.

    By the way, I assume that on Reports the list of serial numbers is unique, is this correct?
    Last edited by Jacc; 06-29-2014 at 05:21 AM.

  11. #11
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: sumproduct and/or index/match(?)

    Here is an upgrade with year as well. It's not really tested, that will be your task.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    11-23-2013
    Location
    Washington DC
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: sumproduct and/or index/match(?)

    Magnificent!

    You're absolutely correct to assume the Report serials are unique. I think the task would be nearly impossible otherwise (I mean, Excel will count, but it won't be accurate per se). At any rate, the helper columns serve an unanticipated second function in that they show grades through the entirety of the dataset, which has its own inherent usefulness.

    Really, none of the several Excel books I have detail helper columns, yet they are immensely useful. I understand the syntax of the column itself, but the strategy of nailing it down per month eludes me a bit. Nevertheless, it's great education. Thanks again!

  13. #13
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: sumproduct and/or index/match(?)

    ------------
    Last edited by Jacc; 07-01-2014 at 02:46 AM.

  14. #14
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: sumproduct and/or index/match(?)

    I think I made a misstake... Take a look at 20100513 at Reports. I have used the date on that row to get the month (March). However, in Evaluations 20100513 appear in April as well. That means that this will never be counted in my first solution, this must be wrong!

    Ignoring the datecolumn on Reports and just going by the year and month stated on Departments makes more sense now that I think about it. Is this correct?

    Also, made a lookup for the month, easier for me when testing.
    Attached Files Attached Files
    Last edited by Jacc; 06-29-2014 at 10:26 AM.

  15. #15
    Registered User
    Join Date
    11-23-2013
    Location
    Washington DC
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: sumproduct and/or index/match(?)

    I see what you did:

    Old:
    =SUM(IF(IF(ISNUMBER(SEARCH($B2,IF(YEAR(Evaluations!$D$2:$D$7)&MONTH(Evaluations!$D$2:$D$7)=YEAR(Reports!$C2)&MONTH(Reports!$C2),Evaluations!$B$2:$B$7))),Evaluations!$C$2:$C$7)=D$1,1))

    New:
    =SUM(IF(IF(ISNUMBER(SEARCH($B2,IF(YEAR(Evaluations!$D$2:$D$7)&MONTH(Evaluations!$D$2:$D$7)=Departments!$I$1&Departments!$J$2,Evaluations!$B$2:$B$7))),Evaluations!$C$2:$C$7)=D$1,1))

    Yes, the edit seems much more reliable.

    Meanwhile, you set the month number formula to =MATCH($I$2,$M$4:$M$15,0) from =MONTH(DATEVALUE("1 "&I2)). I can't say I understand the underlying difference, although I see the new formula specifically looking at the month listing.

    So you're having the formula look directly at the year/month in $I$1 and $J$2 respectively.

    I'll give your edits a go on the live sheet, and I'll let you know how it turns out. Thanks again for your help and expertise!

  16. #16
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: sumproduct and/or index/match(?)

    Quote Originally Posted by jimbosi View Post
    Yes, the edit seems much more reliable.
    That answer is too vague! These two formulas produce different results! Make sure that you understand the issue with 20100513 described in my last post and do the research. Maybe it is not a problem on the real data, just in your sample data?

    Should the date column on Reports be ignored? What do those dates represent anyway?

  17. #17
    Registered User
    Join Date
    11-23-2013
    Location
    Washington DC
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: sumproduct and/or index/match(?)

    I'll check it when I get to the live sheet.

    As far as dates, that's upon what the data hinges.

    Specifically, a Department writes a report in Mar. Whether it's evaluated in Mar, Jul, Oct, or Dec, it should be counted each time against Mar, and then secondarily by the grade it got.

    So in the above instance, a particular serial would be counted four times in Mar; one for each month, by whatever grade it got each time.

    The date in Reports is the date it was issued. The date in Evaluations was the date in which the evaluation and grade was written.

    Do the differences in your formulas produce different results in your observation, knowing the count should be four in the above instance?

    I'll recheck it on the live sheet. Thanks again for the assistance!

  18. #18
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: sumproduct and/or index/match(?)

    Quote Originally Posted by jimbosi View Post
    So you did helper columns with an array in Reports that totaled the grades regardless of month, then just a SUMPRODUCT in Departments.
    Actually no, I didn't and that's where it went wrong.
    rev 4 attached here does just that, collects all grades regardless of month (ignoring the dates on the Evaluations sheet).

    I brought back rev 2, hit Delete a number of times and Voilá! rev 4. The array formulas are a lot shorter now that they don't look for dates.
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    11-23-2013
    Location
    Washington DC
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: sumproduct and/or index/match(?)

    Jacc,

    I tried to send you a PM to answer your question, but the server responded saying your queue is full. I'll post here also.

    You bet. Also, the live sheet has over 30000 rows in Reports and 11000 rows in Evaluations. I'm finding the formulas rather computationally intensive.

    I don't understand how in the pivot table the number grades don't get counted, but the letter grades do, but in the Departments sheet the number grades get counted.

    For instance, I see reports that have number grades, but the corresponding helper column shows 0. It's very confounding.

    At any rate, I immeasurably appreciate your help. Thanks again.

  20. #20
    Registered User
    Join Date
    11-23-2013
    Location
    Washington DC
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: sumproduct and/or index/match(?)

    Jacc,

    I tried to send you a PM to answer your question, but the server responded saying your queue is full. I'll post here also.

    You bet. Also, the live sheet has over 30000 rows in Reports and 11000 rows in Evaluations. I'm finding the formulas rather computationally intensive.

    I don't understand how in the pivot table the number grades don't get counted, but the letter grades do, but in the Departments sheet the number grades get counted.

    For instance, I see reports that have number grades, but the corresponding helper column shows 0. It's very confounding.

    At any rate, I immeasurably appreciate your help. Thanks again.

  21. #21
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: sumproduct and/or index/match(?)

    Well, pretty much anything you do with 30000 rows is computational intensive if you ask me. Array formulas are known for being more computationally intensive but in this case there aren't really any options. The only advice I can give is not to extend the formulas range longer than you have data, i.e. telling the formulas to go all the way to 100 000 just to have some room to grow.

    Not sure what you mean with number grades, is that a number instead of the A, B, C, D, E? I replaced E with 77 and it works just fine putting in 77 as a grade and get it counted. You are sure you are not fooling yourself by having the wrong month or something like that? It has thrown me off a couple of times.

  22. #22
    Registered User
    Join Date
    11-23-2013
    Location
    Washington DC
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: sumproduct and/or index/match(?)

    I assume you're talking about your "jacc4" sheet, right? I quadruple-checked my input of the formulas, and I still get the funny results with only counting number grades in Departments, and only counting letter grades in the pivot (using helpers). Maybe everything is still calculating?

  23. #23
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: sumproduct and/or index/match(?)

    Can you achieve what you want in the jacc4 workbook or is it also causing trouble when you change the grades to numbers?

  24. #24
    Registered User
    Join Date
    11-23-2013
    Location
    Washington DC
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: sumproduct and/or index/match(?)

    The SUM array works fine, and I take that and translate that into a pivot. The monthly Departments tab doesn't work right yet. For now, the pivot is fine because the data set is so large, it's constantly "calculating." Even if I got Departments fixed, I'm not sure it would be responsive enough. I'll take another close look at the Departments SUMPRODUCT to see why it's not calculating properly. Thanks again!

  25. #25
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: sumproduct and/or index/match(?)

    Any progress?

  26. #26
    Registered User
    Join Date
    11-23-2013
    Location
    Washington DC
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: sumproduct and/or index/match(?)

    The SUM(IF . . . portion in Reports works perfectly. Since, many have been clamoring for the results, knowing now such a calculation is possible. The per-month in Departments is another matter. We're going to a new grading system, so I may retry it based on the new grading scheme. I'll attack it either tomorrow or the next day and let you know the results. Kudos again to you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  2. [SOLVED] Using SUMPRODUCT, MATCH and INDEX together
    By codyryan in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-19-2014, 08:08 AM
  3. Replies: 13
    Last Post: 12-13-2012, 11:44 AM
  4. Replies: 5
    Last Post: 04-20-2012, 08:54 AM
  5. Sumproduct or Match & Index?
    By jennyaccord in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-19-2011, 06:41 AM

Tags for this Thread

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