Search code examples
google-sheetscomparearray-formulasgoogle-sheets-formula

calculate percentage from two separate sheets

I can't quite wrap my head around how to do the following: I have two sheets, with some results that reflect some counts. I want to calculate a percentage of the counts between the two sheets. The issue is that the data is dynamic, and the second sheet does not always have matching results... And the results are LARGE.

Here's a sample sheet: https://docs.google.com/spreadsheets/d/1b3ap33kW7ErwF3PcwGPvtv_I3tR0TNdyuurJdHY1Fjk/edit?usp=sharing

I do know that for just a basic array formula percentage I can use this: 

=arrayformula(iferror(A2:A/B2:B))

This work as long as the columns always match but in this case, they won't.
As seen on the sample sheet linked, I was able to achieve the results in the second tab by getting the "unique" from the first tab, but that shows users that did NOT have "reworked" tickets. I want to clean it up so that the only percentages shown are for assignees that do have reworked tickets.

I'm not sure how to tell it to only calculate if there's a "match" from '90 day resolved reworked'!B2 and '90 day resolved'!B2

Any insight would be greatly appreciated...

Solution

  • for a count, you can use QUERY formula:

    =QUERY(A2:A, "select A,count(A) where A is not null group by A label count(A)''", 0)

    0

    to align things you can use VLOOKUP formula:

    =ARRAYFORMULA(IFERROR(VLOOKUP(C:C, F:G, {1,2}, 0)))

    0

    if you want to calculate straight the percentage without QUERY midstep do:

    =ARRAYFORMULA({UNIQUE(FILTER(A2:A,A2:A<>"")),
 TEXT(QUERY(A2:A, "select count(A) where A is not null group by A label count(A)''")/
 IFERROR(VLOOKUP(UNIQUE(FILTER(A2:A,A2:A<>"")), 
 QUERY('90 day resolved'!A2:A, "select A, count(A) group by A", 0), 2, 0)),"#.00%")})

    0

    demo spreadsheet