Sumproduct - Sumifs - Choose

[baseball17bucks]

Hi there,

I have two tables for labor rates by function and by year. One table for GBP currency, and one for USD currency.

I then have a table that contains labor hour requirement by contract (many rows). Each contract is either native to GBP or USD.

I'm trying to use the following formula to multiply the Hours by Function by Year using the labor rates I have in the two mentioned above.

The formula below works fine:

=(SUMPRODUCT(INDIRECT("tbl_OSGMData["&$A23&"HRS;"&C$22&"SP]"),tbl_OSGMData[[P_comp]:[P_comp]],1*(tbl_OSGMData[[P_comp]:[P_comp]]>=0.25),SUMIF(qry_FX_Rates[[Currency]:[Currency]],tbl_OSGMData[[Local Cur.]:[Local Cur.]],qry_FX_Rates[[FX to USD]:[FX to USD]]),SUMIF(OSGM!$A$2:$A$3,tbl_OSGMData[[Local Cur.]:[Local Cur.]],C68#))+(K15+K7)*C6+(R7+R15)*C14)/1000

The C68# term is the result of this formula: =TRANSPOSE(CHOOSE({1,2},C6,C14)) which is a 2x1 vertical array that solves to={73;213} when pushing F9 in the formula bar. These are my two desired labor rates for this cell.


HOWEVER....
The following DOES NOT WORK and I can't seem to figure out why.

=(SUMPRODUCT(INDIRECT("tbl_OSGMData["&$A23&"HRS;"&C$22&"SP]"),tbl_OSGMData[[P_comp]:[P_comp]],1*(tbl_OSGMData[[P_comp]:[P_comp]]>=0.25),SUMIF(qry_FX_Rates[[Currency]:[Currency]],tbl_OSGMData[[Local Cur.]:[Local Cur.]],qry_FX_Rates[[FX to USD]:[FX to USD]]),SUMIF(OSGM!$A$2:$A$3,tbl_OSGMData[[Local Cur.]:[Local Cur.]],TRANSPOSE(CHOOSE({1,2},C6,C14))))+(K15+K7)*C6+(R7+R15)*C14)/1000

Does anyone know how to make this work? Thanks for any and all help!

 

[Domenic]

Syntax

SUMIF(range, criteria, [sum_range])

With SUMIF, the first argument (range) and the third argument (sum_range) must be Range objects, not arrays. In your working formula, you're referring to a range of cells for its sum_range. Whereas in the one that doesn't work, you're referring to an array, specifically the array returned by the formula TRANSPOSE(CHOOSE({1,2},C6,C14)) , hence the error.

 

[baseball17bucks]

With SUMIF, the first argument (range) and the third argument (sum_range) must be Range objects, not arrays. In your working formula, you're referring to a range of cells for its sum_range. Whereas in the one that doesn't work, you're referring to an array, specifically the array returned by the formula TRANSPOSE(CHOOSE({1,2},C6,C14)) , hence the error.

Is there a way for me to create a "range object" from disparate cells, C6 and C14?

 

[baseball17bucks]

Is there a way for me to create a "range object" from disparate cells, C6 and C14?

Thanks for the help!!! I tried your OFFSET suggestion but I now get an #N/A error. In screenshot below, I show the comparison of the output for OFFSET vs. C68# (I'm only showing this small portion of the overall formula).

 

[Domenic]

Try the following...

MMULT(IF(tbl_OSGMData[[Local Cur.]:[Local Cur.]]=TRANSPOSE(OSGM!$A$2:$A$3),CHOOSE({1,2},C6,C14),0),ROW(OSGM!$A$2:$A$3)^0)

By the way, I would suggest that you specify in your profile which version of Excel you're using so that the most efficient solution can be offered.

Hope this helps!