Multiple Variables - Sumif error

[gustavo.simoes1]

Hi everyone,



I’m trying to write a formula that does the following operation. For a product, “T-shirt “ for example, this formula gives me the sum of the all gross revenue source, that means 11 + 33 +22 equals 66. However I need that the same expression be capable to give me also the sum for all net revenue sources for the T-shirt, 3 + 13 +5=21, and for some other criteria as COGS and taxes for the same product, and other products.



In a dumb way I can use a lot of vlookup or sumif, but I looked for a smart way to solve this problem, because there are too many products and types



I tried to use the expression “sumproduct “ (sumif [criteria,interval_range,sum_range]) with a code table formed by the type name (Net Revenue, Cogs, etc) and the product (T-shirt, shoes, etc.), for example “NetRevenuecompanystoreT-shirt” and “NetRevenuelicensedstoreT-shirt”, etc.



This formula works pretty well when the data is in the same sheet, however when I need to recuperate the data in other files I have the #value error problem. I think I have a network problem with the sumif, so I think I need to avoid the use of this function.



Someone can give me a help to solve this problem ?



2008 2009 2010

Gross Revenue company store:

T-Shirt 11 12 13

Shoes 14 15 16

Pants 17 18 19

Gross Revenue licensed store:

T-Shirt 33 32 33

Shoes 34 35 36

Pants 37 38 39

Gross Revenue multi-brand store:

T-Shirt 22 22 23

Shoes 24 25 26

Pants 27 28 29



2008 2009 2030

Net Revenue company store:

T-Shirt 3 2 3

Shoes 4 5 6

Pants 7 8 9

Net Revenue licensed store:

T-Shirt 13 12 13

Shoes 14 15 16

Pants 17 18 19

Net Revenue multi-brand store:

T-Shirt 5 2 3

Shoes 4 2 6

Pants 9 1 4



I hope you understood my English (English is not my mother tongue) and my problem, and someone will be capable to help me!



Thanks

[DonkeyOte]

I would suggest posting a sample file - your layout is not very clear.

If product is in A, 2008 is B, 2009 C etc... then to SUM T-Shirt revenue for 2008

=SUMIF(A:A,"T-Shirt",B:B)

[gustavo.simoes1]

Hi DonkeyOte,

I'm trying to build a sort of "income statement" for a product line, each one composed by more then two products. Therefore I need to recuperate the net revenue, the cogs, the taxes, the Sg&A etc, for each of then.

In the file you can see the problem layout, and how i tried to solve, but my solution doesn't work because the sumif error. I need to recuperate the data in other files, so I have the value error when the source file is closed.

Thanks,

Gustavo

link to the file:

http://www.megaupload.com/?d=I1PCQ13V

[DonkeyOte]

Gustavo, please upload files here directly (via paperclip icon in reply window - click Go Advanced if you can not see it).
People are traditionally wary of downloading files from alternative sites.

[teylyn]

DQ, would you consider moving this thread to a question forum?

[DonkeyOte]

good spot...moved

[gustavo.simoes1]

Sorry, I didn't see the question forum. Shame on me =p

Ps:I uploaded the file using the forum tools.

[teylyn]

Ok, see what you can do with the attached.

I've made a few changes required to accommodate a pure SUMPRODUCT without a nested SUMIF. The error you encountered probably comes from the fact that SUMIF does not work with closed workbooks. See if this is better:

I got rid of the concatenation of product line codes and store codes in the Net_Revenue Sheet and the Income Statement sheet. You'll have to do the same stuff for the other two sheets and the Cogs and Taxes sections on the Income Statement. I've highlighted the cells I changed in pink. Note the single cell naming of the product lines at the top of the sheet. You could put these cells somewhere else, maybe on the Codes_Table Sheet and refer to them there, if they screw up your spreadsheet design.


The formula in F5 is

=SUMPRODUCT(--(ISNUMBER(MATCH(Net_Revenue!$B$3:$B$30,$C$1:$F$1,0))),--(Net_Revenue!$A$3:$A$30=$B5),(Net_Revenue!D$3:D$30))

The way the formula is constructed, it can be copied down and across without the need for changes to the ranges. Note the careful placement of absolute and relative cell addresses, especially the last argument, because that will increment the column to the required month.

The formulas for the other sections should be along the same lines, just different sheet names (and removing the concatenation!!!)

Let me know how you get on with this

cheers

[gustavo.simoes1]

Thanks teylyn!! It works perfectly fine, it will save me a lot of time!