Excel Challenge - Assist Bringing Totals From One Sheet To Another Using Validation List


Hoping someone can assist.  Im looking for either a formula or code that will do the following:
 Once a position is selected in Developers tab cell B3, a formula or code will sum the totals for various stages from the Open Position Data tab and place those totals in the appropriate cells on the Developer tab (See Example File).

For Example:
If Quaterback is in cell B3 (Developers tab), if would show 1 in cell H13 because there is only one row listed in the Open Position Data tab showing a Quarterback who has a status of New- To Be Evaluated and the applicant is external.  The code or formula will need to take into consideration if the position is internal or external by looking at row F of the Open Position Data tab.

A second example would be the position of Waterboy which would show a 2 in cell K19 because there are two applicants, listed as waterboy, with a status of Pre-Screen - Left a Message, that are both External.

Any assistance would be greatly appreciated!!!!!
Who is Participating?
byundtConnect With a Mentor Commented:
To handle 3 jobs at once, use a formula like this:
=SUMPRODUCT((('Open Position Data'!$B$1:$B$1000=$B$3)+('Open Position Data'!$B$1:$B$1000=$B$4)+('Open Position Data'!$B$1:$B$1000=$B$5))*('Open Position Data'!$C$1:$C$1000=K$11)*('Open Position Data'!$F$1:$F$1000="No"),'Open Position Data'!$D$1:$D$1000)

To get All positions, just remove the test for column B:
=SUMPRODUCT(('Open Position Data'!$C$1:$C$1000=K$11)*('Open Position Data'!$F$1:$F$1000="No"),'Open Position Data'!$D$1:$D$1000)
You need to make sure that the descriptions in worksheet Open Position Data column C match those in worksheet Developers rows 11 and 17. Assuming that this is the case, you can use formulas like:
=SUMPRODUCT(('Open Position Data'!$B$1:$B$1000=$B$3)*('Open Position Data'!$C$1:$C$1000=H$11)*('Open Position Data'!$F$1:$F$1000="Yes"),'Open Position Data'!$D$1:$D$1000)

It is worth noting that the descriptions in Developers row 17 omitted "Pre-Screen - ", but that can be prefixed in the formula:
=SUMPRODUCT(('Open Position Data'!$B$1:$B$1000=$B$3)*('Open Position Data'!$C$1:$C$1000="Pre-Screen - " & K$17)*('Open Position Data'!$F$1:$F$1000="Yes"),'Open Position Data'!$D$1:$D$1000)

The sample workbook attached shows these formulas where I saw matching description fields. Where a description was present on Developers, but missing from Open Position Data, I omitted the formula.

I started off using the entire column in the SUMPRODUCT, but there was a delay of a second or so for each formula to update. Such a delay would be intolerable in a production situation, so I restricted the formula to rows 1 through 1000. The formula should extend beyond any anticipated amount of data, but stop well short of the million plus rows that are possible in a .xlsx workbook.
EscanabaAuthor Commented:
Greatly appreciated.  If I wanted to get a sum of multiple positions at once how would I adjust the formula or is that even possible?  For example, instead of a drop down list there are 3 positions listed starting at cell B3 to B6 (Quarterback, Receiver and Waterboy).  Is it possible for SUMPRODUCT to work on multiple cells?

I dont want to take advantage so if you feel this needs to be a separate question please let me know and I can make another one.
EscanabaAuthor Commented:
Or the other option would be to add "All" to the validation list and have the formula pull totals for all the positions.
EscanabaAuthor Commented:
Perfect!  Thank you!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.