Excel - How to Write a formula using SUMPRODUCT 2

[wec43wec]

I am attemtping to create "two" different types os "SUMPRODUCT" formulas in excel using the DATA BASE below for each problem / formula


RAW Data Base

Col A Col B Col C Col D Col E


1. Sales State Date Apples Oranges
2. 100 CA 1-Jun-10 100 300
3. 200 TX 1-May-10 100 300
4. 300 CA 1-Apr-10 100 300
5. 400 TX 1-Jun-10 100 300
6. 500 CA 1-May-10 100 300
7. 600 TX 1-Apr-10 100 300
8. 700 CA 1-Jun-10 100 300
9. 800 TX 1-May-10 100 300

Formula / Question # 1 - is there a easier way to create the formula below that is used to sum data in Col A based on identifying "3" different dates in Col C? The formula works. I was just wondering is there a way I could write a formula using less code?

=SUMPRODUCT((A10:A17)*(B10:B17="CA")*(C10:C17=C12))+SUMPRODUCT((A10:A17)*(B10:B17="CA")*(C10:C17=C10))+SUMPRODUCT((A10:A17)*(B10:B17="CA")*(C10:C17=C16))

Formula / Question # 2


Below is the "second" question using the same data source above, however range names were used instead of cell references /addresses.

I am attemtping to determine the "percent" of apples to the combined total of apples and oranges.


The formula I tried to write which does not work is indicated below:

I would like the formula to read /say - Apples / (Apples + Oranges)= % of fruits using a data base that SUMPRODUCT is used to separate by state.

=SUMPRODUCT(((Apples)*(State="CA")/((SUMPRODUCT(Apples)*(State="CA")+(SUMPRODUCT(Oanges)*(State="CA")))

 

[GlennUK]

First formula:

=SUMPRODUCT((A10:A17)*(B10:B17="CA")*OR(C10:C17=C12,C10:C17=C10,C10:C17=C16))

Note that C10 and C16 look the same if I am reading your data correctly.

... and second formula:

=SUMPRODUCT((Apples)*(State="CA"))/(SUMPRODUCT((Apples)*(State="CA"))+SUMPRODUCT((Oranges)*(State="CA")))

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

hi,

Your problem is the structure of your data. Your table is not normalized. Your "RAW" data appears to be a PIVOT SUMMARY REPORT, rather than RAW data.

A RAW data table might look like this and will be much more suited to Excel's many table-related analysis and report features...
[tt]
Sale Amt State Date Comodity Volumn
123.45 CA 1/2/2010 Apple 27.6
45.67 CA 3/2/2010 Oranges 5.4
[/tt]
Functions like SUMPRODUCT, work better in this data structure.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[wec43wec]

Glenn - the formula you created does not work / I get an # DIVO error in the second problem.

The first solution does not appear to work for me. The "or" code should be a "and" code because I need to add/sum data after test certain dates. The formula gives me a sum regardless of my test for data when using data thathas more then 9 rows.


Maybe I am keying something incorrectly, however if you can take a second look at your formula I would greatly appreciate it.

Note - I am using a Blackberry cell to respond.


Skip - the data is raw data. I normal use a pivot table to generate my results which I am trying to automate using sumproduct in my report template that will pull data from my download of raw data.
Using the information I provided, can you give another suggestion or are you in agreement. With Glenn.

Thanks.

 

[SkipVought]

Maybe it is RAW to you, but it is DEFINITELY NOT RAW DATA!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Normalize your "RAW" data.
[tt]
State Date Sales Commodity ComVal
CA 1-Jun-10 100 Apples 100
TX 1-May-10 200 Apples 100
CA 1-Apr-10 300 Apples 100
TX 1-Jun-10 400 Apples 100
CA 1-May-10 500 Apples 100
TX 1-Apr-10 600 Apples 100
CA 1-Jun-10 700 Apples 100
TX 1-May-10 800 Apples 100
CA 1-Jun-10 100 Oranges 300
TX 1-May-10 200 Oranges 300
CA 1-Apr-10 300 Oranges 300
TX 1-Jun-10 400 Oranges 300
CA 1-May-10 500 Oranges 300
TX 1-Apr-10 600 Oranges 300
CA 1-Jun-10 700 Oranges 300
TX 1-May-10 800 Oranges 300
[/tt]
The formula...
[tt]
=SUMPRODUCT((Commodity="Apples")*(State="CA"))/(SUMPRODUCT((Commodity="Apples")*(State="CA"))+SUMPRODUCT((Commodity="Oranges")*(State="CA")))
[/tt]
The result 0.5, based on COUNTS alone.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[wec43wec]

Skip - maybe using the term raw was incorrect but this format is the only way I can retrieve the data which prompt me to use the term - raw.

Currently, I use the PT to generate my report which I am trying to learn the sumproduct function to make it easier.

 

[SkipVought]

I just corrected your forumla
[tt]
=SUMPRODUCT((Apples)*(State="CA"))/(SUMPRODUCT((Apples)*(State="CA"))+SUMPRODUCT((Oranges)*(State="CA")))
[/tt]
got .25 as result.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

my structure/formula when modified for summing the ComVal...
[tt]
=SUMPRODUCT((Commodity="Apples")*(State="CA")*(ComVal))/SUMPRODUCT((State="CA")*(ComVal))
[/tt]
This is how a good table performs. I get the sum of ONE FRUIT and the sum of ALL FRUITS very simply!

Which will perform MUCH better when you begin to get into the real word, including, Cherries, Lemons, Grapefruit, Pomegranates, Kiwi...... ad nausium.

Do you want to ADD all those in the demoninator??? FOOLISHNESS!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[wec43wec]

Skip - thank for the quick response.

I only have one other question using the data you provide in your last response.

If the date Col.had multiple dates, how do I include in your formula a test to only add data for certain dates.

As an example I may want to only sum data for Oct 1, 2010, Nov 1, 2010 and Dec 1, 2010 (which currently are not dates listed in your example).


Is this possible?

Thanks for the help and clarity thus far.

 

[SkipVought]

[tt]
=SUMPRODUCT((Commodity="Apples")*(State="CA")*(Date>=G2)*(Date<=H2)*(ComVal))/SUMPRODUCT((State="CA")*(Date>=G2)*(Date<=H2)*(ComVal))
[/tt]
assuming that these are Low and High date values, G2 is the lower criteria and H2 is the higher criteria.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}