Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct w/ multiple factors: Correct and Incorrect return value
My conundrum:
I'm using sumproduct formulas with multiple factors to extract totals from another worksheet (my data source). I'm tabulating the number of entries whose adjacent cells meet various criteria. Using virtually identical sumproduct formulas in my tabulating worksheet, the results are sometimes 'correct' (desired) output, and sometimes they seem to miss counting one or two entries in the source data which actually meet the criteria of the formula. I have checked formatting (and extra 'spaces') of the data source and of the cells with the formulas... and I can't figure out what is wrong. Any help is much appreciated. --Rachel... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct w/ multiple factors: Correct and Incorrect return value
Rachel,
One idea: Stop using formulas, and start using Pivot Tables. Pivot tables will also help you find values that look close but aren't actually - a string with an extra space, etc. HTH, Bernie MS Excel MVP "Rachel" wrote in message ... My conundrum: I'm using sumproduct formulas with multiple factors to extract totals from another worksheet (my data source). I'm tabulating the number of entries whose adjacent cells meet various criteria. Using virtually identical sumproduct formulas in my tabulating worksheet, the results are sometimes 'correct' (desired) output, and sometimes they seem to miss counting one or two entries in the source data which actually meet the criteria of the formula. I have checked formatting (and extra 'spaces') of the data source and of the cells with the formulas... and I can't figure out what is wrong. Any help is much appreciated. --Rachel... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct w/ multiple factors: Correct and Incorrect return v
Hello Bernie...
I am unfamiliar with using pivot tables in this way. Question: Does it mean I will have more 'worksheets' with 'the same' data? i.e. will I need to be saving all the pivot table(s) some place in order to then tabulate things from it(them)? If so, I was hoping for a way to tabulate without 'duplicating' my datasource... it's large, with over 1600 entries, and still counting... What's your thought on this? --Rachel. "Bernie Deitrick" wrote: Rachel, One idea: Stop using formulas, and start using Pivot Tables. Pivot tables will also help you find values that look close but aren't actually - a string with an extra space, etc. HTH, Bernie MS Excel MVP "Rachel" wrote in message ... My conundrum: I'm using sumproduct formulas with multiple factors to extract totals from another worksheet (my data source). I'm tabulating the number of entries whose adjacent cells meet various criteria. Using virtually identical sumproduct formulas in my tabulating worksheet, the results are sometimes 'correct' (desired) output, and sometimes they seem to miss counting one or two entries in the source data which actually meet the criteria of the formula. I have checked formatting (and extra 'spaces') of the data source and of the cells with the formulas... and I can't figure out what is wrong. Any help is much appreciated. --Rachel... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct w/ multiple factors: Correct and Incorrect return v
Rachel,
Pivot tables access your data from the source table, and you can have as many pivot tables based on one database as you want (there probably is an upper limit, but none that I have ever come upon). Pivot tables can go onto a new sheet, or onto an existing sheet. The increase in file size isn't that great - I haven't compared it to using formulas to extract the same data, but the chance for error is much less, as long as you follow a few basic guidelines: primary among them are that you need to refresh the PT when the data changes, and that you need to insert rows for new data within the old table rather than appending it to the end. Also, if you are linking from the PT, you need to use GETPIVOTDATA instead of linking to cells. 1600 items is small-ish as databases go, and shouldn't be a problem. PT run into problems when the number of unique key values (rows in the resulting PT) is, IIRC, over 8,192.... HTH, Bernie MS Excel MVP "Rachel" wrote in message ... Hello Bernie... I am unfamiliar with using pivot tables in this way. Question: Does it mean I will have more 'worksheets' with 'the same' data? i.e. will I need to be saving all the pivot table(s) some place in order to then tabulate things from it(them)? If so, I was hoping for a way to tabulate without 'duplicating' my datasource... it's large, with over 1600 entries, and still counting... What's your thought on this? --Rachel. "Bernie Deitrick" wrote: Rachel, One idea: Stop using formulas, and start using Pivot Tables. Pivot tables will also help you find values that look close but aren't actually - a string with an extra space, etc. HTH, Bernie MS Excel MVP "Rachel" wrote in message ... My conundrum: I'm using sumproduct formulas with multiple factors to extract totals from another worksheet (my data source). I'm tabulating the number of entries whose adjacent cells meet various criteria. Using virtually identical sumproduct formulas in my tabulating worksheet, the results are sometimes 'correct' (desired) output, and sometimes they seem to miss counting one or two entries in the source data which actually meet the criteria of the formula. I have checked formatting (and extra 'spaces') of the data source and of the cells with the formulas... and I can't figure out what is wrong. Any help is much appreciated. --Rachel... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct issues | Excel Worksheet Functions | |||
SUMPRODUCT returning incorrect result | Excel Worksheet Functions | |||
A correct formula gives an incorrect figure | Excel Worksheet Functions | |||
Anyone seen Pivot Tables with incorrect detail but correct total? | Excel Discussion (Misc queries) |