I am trying to write a formula in excel.
Sheet A col A = identifier
Sheet B col A = identifier
Sheet B col B = year (1,2,3 etc.)
Shett B col C = salary
On Sheet A col B
I want to get the sum of salary in Sheet B col C that matches the identifier in Sheet A col A, but only for years marked as 1.
Can anyone help?
Assuming that your data start in A1, try this in B1 and copy down.
=SUMPRODUCT((Sheet2!$A$1:$A$100=A1)*(Sheet2!$B$1:$B$100=1)*(Sheet2!$C$1:$C$100))
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Thank you for this.. this returns a 0. ( better than an errror though
Hi Knocknaboula
Try the sumproduct below.
=SUMPRODUCT(('Sheet A'!A:A="identifier")*('Sheet B'!A:A="identifier")*('Sheet B'!B:B=1)*('Sheet B'!C:C))
Last edited by Kevin UK; 03-06-2013 at 07:47 AM.
Regards Kevin
Merged Cells (They are the work of the devil!!!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks