A while ago I found a great Function in Excel called SUMPRODUCT. This allowed me to SUM columns based on multiple criteria as opposed to one using SUMIF (only 1 criteria). Also got rid of the necessity of using SHIFT – CTRL – ENTER to enter an Array formula (as I would invariably forget if I edited the formula).
This function is great but like arrays it slows the spreadsheet down (takes longer to calculate). Also the range has to be specified so you cannot just select the whole Column to sum (like ARRAYS). This causes problems when the data you are calculating comes from columns whose range is likely to change.
So another great Function to add to the SUMPRODUCT is INDIRECT. This basically turns your range into text so it will never change.
An example:
=IF(C31>0,(((SUMPRODUCT((INDIRECT("'Data Sheet'!$O$2:$O$8000")=C1)*(INDIRECT("'Data Sheet'!$E$2:$E$8000")=10142)*INDIRECT("'Data Sheet'!$J$2:$J$8000")))+(SUMPRODUCT((INDIRECT("'Data Sheet'!$O$2:$O$8000")=C1)*(INDIRECT("'Data Sheet'!$E$2:$E$8000")=10142)*INDIRECT("'Data Sheet'!$H$2:$H$8000"))))/C31),0))
This is nested in an IF statement and is basically 2 SUMPRODUCTS added together and then divided by C31 IF C31 is greater than 0. If C31 is less than or equal to 0 then 0 is returned to the cell. As you can see each SUMPRODUCT will add values in columns J and H respectively if certain criteria are met in Columns O and E. Of course more criteria can be added if required
Pretty nifty BUT having 700 of these or similar gives me time to go and have a leak, have a smoke and a cup of coffee while the Spreadsheet is calculating. Also trying to figure out where to put the parentheses is a bit of a nightmare. I got each SUMPRODUCT to work independently, then added the INDIRECT, put them together then added the IF statement.
Comments
Love and kisses from your wife
xxx