CODEWITHSUNDEEP
google-sheets
Brian Gall
Brian Gall

Reputation: 1

Google Sheets - Count of compared rows based on another cell

How would I build a formula in Google Sheets that counts the number of rows assigned to a particular manager where the value in column C does not match the corresponding row in column D. Basically I need to compare the calculated hours to the approved hours in each row for the employees and count the number of rows where the Approved hours for the employee do not match the Calculated Hours

Example:

Employee         Manager        CHours         AHours
Alex             Brian          8              8
Brad             Markus         14             8
Frank            Markus         8              0
John             Brian          8.5            8
Tom              Kenneth        8              6
Zlatan           Kenneth        5.5            0

The result should be:

Manager          Count of Unapproved Hours
Brian            1
Markus           2
Kenneth          2

Upvotes: 0

Views: 314

Answers (2)

player0
player0

Reputation: 1

use:

=INDEX(QUERY({B2:B, IF(C2:C>D2:D, C2:C-D2:D, )}, 
 "select Col1,count(Col2)
  where Col1 is not null
  group by Col1
  label Col1'Manager',count(Col2)'Count of Unapproved Hours'"))

enter image description here

Upvotes: 2

Brian Zollinhofer
Brian Zollinhofer

Reputation: 25

Link to Example Spreadsheet

I made a sheet to show how I would solve it. I would add a column to see if the calculated hours are less than or equal to the approved hours and return a result. I would then use =countifs() to check to see each name and if there were any results that were not approved.

formula for approved/not approved

=if(C3<=D3,"approved","not approved")

formula for counting not approved:

=COUNTIFS($B$3:$B$8,B12,$E$3:$E$8,"not approved")

Upvotes: 0

Related Questions