Lookup "name" in two sheets to make sum

0
votes

I have a printer report with 3 Sheets.

Sheet 1 (User Monthly Report) are where my results are "pasted". User Monthly Report

Sheet 2 (User Usage Report) holds the current printer count. User Usage Report

Sheet 3 (User Usage Report OLD) holds the previous months printer count. (Looks exactly like Sheet 2 (User Usage Report))

In User Monthly Report; Column A there are names. (eg. Kim Pearson)

I want to look up the name in User Monthly Report; Column A (Kim Pearson) in User Usage Report and User Usage Report OLD, and IF the name Kim Pearson appears in both of these sheets, then, it must minus the number in User Usage Report; Column I from the number in User Usage Report OLD; Column I.

NOTE: Kim Pearson is in row 3 on User Usage Report, and in row 2 on User Usage Report OLD. And It will be the same with all other users (They will never be in the same row)

2
vbaexcelexcel-formula

2 Answers

2
votes

I presume when you say minus the number you are referring to the number in Usage Report OLD 

=IF(ISERROR(Match("Kim Pearson",'User Monthly Report'!$A$1:$A$10,0)),0,IFERROR(-Match("Kim Pearson",'User Usage Report OLD'!$E$1:$E$10,0),0)

This returns the negative value from User Usage Report OLD provided the user is in both of the other sheets. Add this formula to the end of your sum formula and replace the hardcoded names with cell references e.g.

=SUM(F2:H2)+IF(ISERROR(Match(E2,'User Monthly Report'!$A$1:$A$10,0)),0,IFERROR(-Match(E2,'User Usage Report OLD'!$E$1:$E$10,0),0)
1
votes

Place this formula in sheet 1, on row 2 :

=IFNA(VLOOKUP(A2,'User Usage Report OLD'!E:I,5,FALSE)-VLOOKUP(A2,'User Usage Report'!E:I,5,FALSE),0)