I'm getting an error with the results of a formula in cell C1. I've tried an Array and a SUMPRODUCT formula but so dice. The problem is some of the cells in the array (A2:A10 and B2:B10) have formulas and I'm getting a "VALUE" error. If I delete the formulas the I get proper results.
Formula in cell C1: =SUMPRODUCTS((A2:A11)*(B2:B11))
My spreadsheet has a list of equipment for 4 different buildings. Each building has a "Quantity" column.
Building 1 = F2:F10
Building 2 = G2:G10
Building 3 = H2:H10
Building 4 = I2:I10
Total = J2:J10
Cell A1 uses a "DATA\VALIDATE LIST" to allow the user to select which building they want to know the quantities from to be displayed in range A2:A10. The cells in this range, have a lookup formula and display the quantity based on the user selection.
The values in A2:A10 are the quantities and B2:B10 is the unit cost for that item.
The value in C1 is supposed to multiply the quantities in A2:A10 by the dollar amount in B2:B10 and display the total cost of all the parts. I was thinking that an ARRAY formula, that tested the Value of the cells in A2:A10 and B2:B10, and only calculates them if the value is greater than 0.001, but I can't seem to get that to work either.
Any suggestions?
Formula in cell C1: =SUMPRODUCTS((A2:A11)*(B2:B11))
My spreadsheet has a list of equipment for 4 different buildings. Each building has a "Quantity" column.
Building 1 = F2:F10
Building 2 = G2:G10
Building 3 = H2:H10
Building 4 = I2:I10
Total = J2:J10
Cell A1 uses a "DATA\VALIDATE LIST" to allow the user to select which building they want to know the quantities from to be displayed in range A2:A10. The cells in this range, have a lookup formula and display the quantity based on the user selection.
The values in A2:A10 are the quantities and B2:B10 is the unit cost for that item.
The value in C1 is supposed to multiply the quantities in A2:A10 by the dollar amount in B2:B10 and display the total cost of all the parts. I was thinking that an ARRAY formula, that tested the Value of the cells in A2:A10 and B2:B10, and only calculates them if the value is greater than 0.001, but I can't seem to get that to work either.
Any suggestions?