If valueA appears in different sheet, sum valueB with conditions

Hi all,

I’m having difficulty writing a formula to solve the following dilemma:

I have “Sheet1” with staff details and each staff member may be listed more than once. I need to use columns C (StaffID) and L (Load). Load values may only be 1.0 or 0.5.

Then I have “Sheet2” which is being used to compile data from a range of sources – this is where I need to put my formula to get my answer. “Sheet2” has column A (StaffID) and each ID will only appear once on Sheet2.

The formula required needs to solve “if the StaffID from Sheet2 appears in Sheet1 (keeping in mind it may appear multiple times), sum the Load values for that StaffID provided the Load value on each line only equals 0.5".

If the StaffID doesn't match, the cell return is to be 0.

I will then need to do this again in another cell where the Load value only equals 1.0 (obviously just amending the condition value in the formula).

I hope that's enough information, and not too convoluted.
TIA.

could you post an example worksheet?
I'd need to know where in the worksheets the staff ID's are to know how to go about checking for one in the other.
It sounds like SUMIFS would work pretty well, but it's hard to say without seeing it.

I've removed all the superfluous (and confidential) data but just left the only cells with the information required.

Hi Gismo, and welcome to the forum.

Since you're using Excel 2007, you can take advantage of the new SUMIFS function. Perhaps this one will suit your needs. Change the final 1 to 0.5 to get the second formula.

=SUMIFS(Sheet1!$L:$L,Sheet1!$C:$C,$A2,Sheet1!$L:$L,1)

You just want to know how many times StaffID "x" has Load value "0.5"? So you're only dealing with columns C and L and you're not adding values from a third column?

If so, a normal SUMPRODUCT() can give you the count, then you just multiply that by the "load value" you used to do the math, yes?

EDITED to match posted sheet:

=SUMPRODUCT(--(Sheet1!$C$1:$C$1000=$A2),--(Sheet1!$L$1:$L$1000=1),Sheet1!$L$1:$L$1000)
=SUMPRODUCT(--(Sheet1!$C$1:$C$1000=$A2),--(Sheet1!$L$1:$L$1000=0.5),Sheet1!$L$1:$L$1000)

Then copy that formula to the right for the next column if C1 has the load value of 1 in it.

I'm a little slow today, here's an example of what paul has there.

Excellent. Thank you.
Just a minor question in regards to the range however - the base data table (Sheet1) needs to be updated at a later stage. I see the range is locked at row 81. Can I just leave the range as $L:$L to keep the lookup open-ended? My only other concern is that someone else will sort either of the data worksheet or the compilation worksheet by numerical order of StaffID or even another column such as Surname/School/Division etc.

In Excel 2007 you can use whole columns with the SUMIFS and SUMPRODUCT functions. In Excel 2003 you cannot use whole columns with SUMPRODUCT. So if you have users still on 2003 that may need to use this workbook, don't use whole columns in your SUMPRODUCT function (can't use SUMIFS in 2003 since it doesn't exist).

Amending the SUMPRODUCT formula for various iterations of conditions has worked well in this sheet - thank you again.

One final issue however, using the example attachment I provided:
Sheet1 has a Start Date column also. Using the similar principle to before, I'd like to know “if the StaffID from Sheet2 appears in Sheet1 (keeping in mind it may appear multiple times), count the number of listings where the Start Date is 01/01/2009 or later"

I'm guessing it's a similar criteria/range formula to before, but I'm seriously failing at this spreadsheet now!!

Be sure to indicate to whom you are addressing your question(s) since multiple people offered multiple solutions.

On your sample sheet, in Sheet2!P2, put this formula and copy down:

=SUMPRODUCT(--(Sheet1!$C$2:$C$1000=Sheet2!A2),--(Sheet1!$K$2:$K$1000>=DATEVALUE("1/1/2009")))

Wonderful. Cheers - all does the trick
Now I just need to figure out why the sheet takes so long to calculate on a quad core processor, but, in the end, as long as the numbers are there I don't really care!

I hope you're not making 100s of those SUMPRODUCT() formulas. They're real performance killers!

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].





(Also, use the "scales" icon in our posts to leave Feedback, it is appreciated)