Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct - reading a variable value
Need help on this one, not a lot of time to try to learn it on my own: I have a list of employees in column A I have list of various job codes in column C with assigned employee number associated with that job code in column D, in range of row 5 through 960. I need the formula to read the employee number in A1, then search for that employee number in D5:D960, then count the number of specific job codes in the associated column C5:C960 Employee 7004 did xx number of TC, SC, NC, DM, etc. Similar to this formula, but the “7004” is a variable that needs to be read from a cell rather be an absolute, and the “TC” needs to read that and about 10 more codes =SUMPRODUCT(($D$5:$D$960={"7004"})*($C$5:$C$960= "TC")) thanks for saving me, again…. j -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=545148 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct - reading a variable value
Isn't it just
=COUNTIF($D$5:$D$960,A1) -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "redneck joe" wrote in message ... Need help on this one, not a lot of time to try to learn it on my own: I have a list of employees in column A I have list of various job codes in column C with assigned employee number associated with that job code in column D, in range of row 5 through 960. I need the formula to read the employee number in A1, then search for that employee number in D5:D960, then count the number of specific job codes in the associated column C5:C960 Employee 7004 did xx number of TC, SC, NC, DM, etc. Similar to this formula, but the "7004" is a variable that needs to be read from a cell rather be an absolute, and the "TC" needs to read that and about 10 more codes =SUMPRODUCT(($D$5:$D$960={"7004"})*($C$5:$C$960= "TC")) thanks for saving me, again.. j -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=545148 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct - reading a variable value
This one is bit ugly, but it can work for multiple job codes:
=SUMPRODUCT(--($D$5:$D$960=$A$1),--(($C$5:$C$960= "TC")+($C$5:$C$960= "SC"))) Adding more pieces of $C$5:$C$960="XX" will add those rows to the count. Hope this helps, Miguel. "redneck joe" wrote: Need help on this one, not a lot of time to try to learn it on my own: I have a list of employees in column A I have list of various job codes in column C with assigned employee number associated with that job code in column D, in range of row 5 through 960. I need the formula to read the employee number in A1, then search for that employee number in D5:D960, then count the number of specific job codes in the associated column C5:C960 Employee 7004 did xx number of TC, SC, NC, DM, etc. Similar to this formula, but the €ś7004€ť is a variable that needs to be read from a cell rather be an absolute, and the €śTC€ť needs to read that and about 10 more codes =SUMPRODUCT(($D$5:$D$960={"7004"})*($C$5:$C$960= "TC")) thanks for saving me, again€¦. j -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=545148 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct - reading a variable value
ugly is as ugly does. Both seem to be working. Bob - I actually needed that one too, although slightly different reason. all beautiful to me. Thanks guys -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=545148 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Aling multiple sets of data by header column | Excel Discussion (Misc queries) | |||
Reading Data from another workbook... depending on variable in a cell? | Excel Discussion (Misc queries) | |||
variable height variable width stacked bar charts | Charts and Charting in Excel | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |