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
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)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
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
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.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
DQ, would you consider moving this thread to a question forum?
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking theicon below the post.
Avoid pie charts with more than two data points. Why? See here (pdf, 559 kb). The only acceptable pie chart is here.
good spot...moved
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Sorry, I didn't see the question forum. Shame on me =p
Ps:I uploaded the file using the forum tools.
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
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.Code:=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 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
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking the
Thanks teylyn!! It works perfectly fine, it will save me a lot of time!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks