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...

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...

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...

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...