SUMPRODUCT( ,SUMIF()) - add 1 to each element in SUMIF array

[BombCenter]

Hey all.

I have a worksheet which has a bunch of products. These products are keyed by a product type. Beneath this product list, there is a subtotal, profit (markup * subtotal), tax, commission, and a final total.

Markup rate, tax rate, commission, etc. can vary based on product type (e.g., some products are not taxable, some are marked up more than others, etc.)

I have a separate worksheet which has a list of each of the product types and their associated markup/tax/commission/etc values.

I have a formula that dynamically looks up the correct values based on the product type, like so:
=SUMPRODUCT(Product.Price, SUMIF(Index!Product.Type, Product.Type, Index!Product.Markup))

However, the values in Product.Markup (or Product.Tax, Product.Commission) are percentage values, and so when I multiply them by the price, I get a result which needs to be added back into the original price to display the final price with markup). Is there a way to add 1 to each of the values returned by the SUMIF array so I get something that behaves more like Product.Price * (Product.Markup + 1) instead of having to do Product.Price + (Product.Price * Product.Markup)? I'm trying to minimize my use of SUMPRODUCT and SUMIF and would prefer to not have to call them unnecessarily.

Thanks.

 

[ExcelElliott]

Give this a try:

=SUMPRODUCT((Product.Price)*(Index!Product.Type=Product.Type)*(Index!Product.Markup+1))

No need for Ctrl+Shift+Enter

 

[BombCenter]

Give this a try:

=SUMPRODUCT((Product.Price)*(Index!Product.Type=Product.Type)*(Index!Product.Markup+1))

No need for Ctrl+Shift+Enter

No joy =(

Get a #VALUE error -- wrong data type.

FYI I plugged in this:
=SUMPRODUCT(OFFSET(Material.Cost,,,ROWS(Material.Cost)-1,) * (Index.Item = OFFSET(CCN.Number,,,ROWS(CCN.Number)-1,)) * (Index.Markup.Rate + 1))

 

[ExcelElliott]

Did you try Ctrl+Shift+Enter. I think the Offset function warrants using it in this scenario.

 

[ExcelElliott]

Also the -1 in the offset I think it is making the array sizes not the same which would cause the SUMPRODUCT function to fail.

 

[BombCenter]

Yep. Tried both ways no joy.
I think SUMPRODUCT handle arrays natively.

 

[BombCenter]

Also the -1 in the offset I think it is making the array sizes not the same which would cause the SUMPRODUCT function to fail.

In the original formula:
=SUMPRODUCT(OFFSET(Material.Cost,,,ROWS(Material.Cost)-1,),SUMIF(Index.Item,OFFSET(CCN.Number,,,ROWS(CCN.Number)-1,),Index.Material.Markup))

Material.Cost and CCN.Number are always of the same size, but they are oversized by 1 cell to accommodate row insertion (since these ranges can contain blanks a traditional dynamic named range might fail), hence the offset and resizing 1 row smaller.
If Material.Cost and CCN.Number are not of the same size then this would indicate a problem with the worksheet structure itself and would render the sheet useless or largely incorrect.
On the index worksheet, Index.Item and Index.Material.Markup are again always of the same size for the same reason.

Basically, I just want to add 1 to every element in the array that's returned by the SUMIF so I don't have to add the markup value back in like this mess below, which is a lot of offsets, sumproducts, and sumifs when scaled up to 500 formulas:
=SUMIF(OFFSET(CCN.Number,,,ROWS(CCN.Number)-1,),"*",OFFSET(Material.Cost,,,ROWS(Material.Cost)-1,)) + SUMPRODUCT(OFFSET(Material.Cost,,,ROWS(Material.Cost)-1,),SUMIF(Index.Item,OFFSET(CCN.Number,,,ROWS(CCN.Number)-1,),Index.Material.Markup))

 

[ExcelElliott]

The only other thing I can see is you arrays within the SUMPRODUCT are not of equal size.

I think in the OFFSET function the Height argument should not include the -1.

Please disregard, did not see your post above prior to submitting.

 

[BombCenter]

No worries, and thank you so much for all your help and suggestions.

However, I think I'll repost this again next year as I am now officially off work and wont be returning until the 3rd.

Happy New Year everyone!
Don't do anything I wouldn't do, and if you do, name it after me.

Be safe all.

KR.