SUMIF formula error with Array

[cjn5465]

Hi there,

I'm trying to create a formula that will provide a total cost based on number of items within a range. I have a formula in my file but it is not returning the correct total. Ultimately each quantity threshold will have a different rate.

# OF ITEMS RATE PER ITEM
1,000,000 $40.00
2,000,000 $40.00
3,000,000 $40.00
4,000,000 $40.00
5,000,000 $40.00
6,000,000 $40.00
0

ITEM # COST
4,678,910 $147,156,400.40

What the total should be $187,156,400.00

Delta/Variance $39,999,999.60

=SUM(IF(IF(A$17>=A7:A12,A7:A12-A6:A11,A$17-A6:A11+0.01)>0,IF(A$17>=A7:A12,A7:A12-A6:A11,A$17-A6:A11+0.01)*B6:B11))

Please let me know if you need any further information.

Thank you for any help you can provide, I greatly appreciate it.

[JeteMc]

There are some problems with the spreadsheet as set up. For one there is a circular reference in B10 where the formula entered is

Please Login or Register  to view this content.

You could solve this by selecting B6 and filling down. Then in cell B17 there is a formula

Please Login or Register  to view this content.

where A$21 is a cell that contains text as opposed to a value. It seems that a formula that multiplies A17 by either a VLOOKUP or INDEX/MATCH formula which refers to B6:B11 is needed in B17. One thing that needs to be clarified is whether the rate per item will be for the next higher or next lower # of items.

[cjn5465]

Thank you for your help. I have corrected the circular reference and updated the sheet to include different rates by increment.Please see attached updated file for clarification and let me know if you have further questions.

[JeteMc]

Which "Total" is correct? Post #1 says $147,156,400.40 but the file attached to post #3 says $143,578,200.00

[cjn5465]

Sorry for the confusion; Post #3 has the correct total since I updated the spreadsheet to include different values based on quantities.

[JeteMc]

To get that total you might do better to use your "Break down" table. Your formulas in column E will work, however they contain an un-needed +. Putting this formula

Please Login or Register  to view this content.

in D6 will get your started (note that you'll have to modify it for cells D7 and down)

[FDibbins]

Why not just this....
=SUMPRODUCT(D6:D10,B6:B10)
No need for the interim calcs

[cjn5465]

Thank you! I've done that temporarily, but I'm really trying to come up with a formula that gives me the total cost based on a plugged quantity. So if I have 2,500,000, it will multiply the 1st million to the $40, the 2nd million to $35 and the remaining 500k to $30. Does this make sense?

[cjn5465]

Thank you! The Break down table will not be in the file, I was using it to explain what I'm trying to accomplish. I want to be able to plug in a quantity to cell A17 and have it automatically calculate. So if I have 2,500,000, it will multiply the 1st million to the $40, the 2nd million to $35 and the remaining 500k to $30. Does this make sense?

[FDibbins]

So the table in A5"B11 wont exist either?

[cjn5465]

That table will remain.

[FDibbins]

OK, can you add a helper column - which can be hidden - to give you what you have in D6? Something like this...

	A	B	C
5	# OF ITEMS	Helper	RATE PER ITEM
6	1,000,000	1,000,000	$ 40.00
7	2,000,000	1,000,000	$ 35.00
8	3,000,000	1,000,000	$ 30.00
9	4,000,000	1,000,000	$ 25.00
10	5,000,000	678,910	$ 20.00
11			$ 20.00
12			$ 143,578,200.00

A6= 1 000 000
A7=IF(A6>$A$17,"",A6+1000000)
copied down

B6=IF(A6="","",IF(ISTEXT(A5),A6,IF(A6>$A$17,$A$17-A5,A6-A5)))
copied down
This column can be hidden

C12=SUMPRODUCT(B6:B11,C6:C11)
You can put this wherever you want it

[cjn5465]

This is Awesome!!! One exception though! If I input 10m, it's not working. What do I need to change in A11 or B11 so that anything > 6m is = to $20?

[JeteMc]

I am sure that there are many fine solutions that would give you an answer. I am simply going back to your "Break down table" (which can be hidden) because, having designed it, you are familiar with how it works. As stated above populate D6 with

Please Login or Register  to view this content.

then D7:D10 with

Please Login or Register  to view this content.

Slide your Totals cells down one row and populate D11 with

Please Login or Register  to view this content.

One change will be needed for the formulas in column E which is if the corresponding cell in column D is empty then the cell in column E is also empty as so

Please Login or Register  to view this content.

I am attaching your file with the above mentioned changes:
Copy of Sumif problem-2.xlsx
Let me know if you have any questions.

[cjn5465]

Thank you very much! This will definitely be the alternative if I can't get the other formula to work appropriately. It's almost there..... Thank you again for your help!!!!