Add using variable criteria

\1

ok so i realized i did not include the Letter and number portions of excel so just assume A,B,C..... 1,2,3 .... ect.

so what i need to do is Add the hours and Overtime Hours of all jobs with the same Job # together. the way i have this set up the job numbers only appear when that job is listed as having over time. the reason it lists regular hours as well is so the accountant will know if all of that day was in over time ,.... or if there are more hours in overtime than hours on that contract it means they worked time in the warehouse as well.

so i have tried working with sumproduct and sum if ,... but all the examples say the criteria is liike " Pen" or something like that ,..... but the job #'s are alwayse changing ,.... how can i tell it to specify a value in a cell and add the next "Bcollumn" of any ACollumn's" that match?

You can create formulas that display the unique values in column B in a list. Then standard SUMIF() formulas on those values can be used.

Are Job numbers ALWAYS numerical? If so, that makes the list very easy to create.

Click GO ADVANCED and use the paperclip icon to post an actual workbook. Make sure there is sample data and mockup the desired results from that sample data completely, we'll help you turn that into formulas.

Like so...

The array formula in U4 would be:
=INDEX($L$4:$L$10, MATCH(0,COUNTIF($L$4:$L$10,"<"&$L$4:$L$10),0))
...confirmed by pressing CTRL-SHIFT-ENTER. A zero should appear.

The array formula in U5 would be:
=IF(COUNTIF($L$4:$L$10,">"&U4), INDEX($L$4:$L$10, MATCH(COUNTIF($L$4:$L$10,"<="&U4), COUNTIF($L$4:$L$10,"<"&$L$4:$L$10),0)),"")
...confirmed by pressing CTRL-SHIFT-ENTER, then copy that cell down through U10.

The normal formula in V5 would be:
=IF($U5="","",SUMIF($L$4:$L$10, $U5, M$4:M$10))

Copy that down and across through W10.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

The arrays will have to be constructed anew in the new location referring to the new ranges. Array formulas are powerful, but a bit of a pain to use.

Remember, the first formula stands alone. Then the second formula can be constructed then copied down to continue the list.

BTW, array formulas can be a performance drain, too. Be wary of constructing formulas that apply to HUGE ranges, and also be wary of putting tons of these formula in a single spreadsheet. You'll see what I mean about performance if you start to do either one of those things.