• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumifs in excel 2003 without using sumproduct

W

weedguru13

New Member
I want to use an alternative for sumifs and have tried using sumproduct, but because of an extremely large data set excel goes really slow.

Was wondering if thr is a vba code i can use to replicate sumifs?

Or any other suggestions?
 
Weedguru13


Firstly, Welcome to the Chandoo.org forums.


Can you give us the outline of what fields you want and matching values

or upload a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook


You may possibly be able to use an Array Entered Sum() formula with some If's inside it
 
Wow. That was quick. Many thanks.


The formula goes something like this :


=SUMPRODUCT(($E$1:$E$2011= $B$2013)* ($D$1:$D$2011=$D2014),J$1:J$2011)

Column B, E and D all refer to string values.


The file has a huge no. of sumifs so i was wondering if i can make a UDF with a syntax similar to sumifs, and replace all the "SUMIFS" with something like "SUMALL" :D i can do without effort i'd have to put in otherwise.


Also, is sumproduct also a volatile function?
 
Weedguru13

Two options for you


=SUM(IF(($E$1:$E$2011= $B$2013)* ($D$1:$D$2011=$D2015),J$1:J$2011)) Ctrl+Shift+Enter


or


=SUM(IF(($E$1:$E$2011= $B$2013),IF(($D$1:$D$2011=$D2016),J$1:J$2011))) Ctrl+Shift+Enter


I think they'll both give you the correct answer
 
Thanks Hui.


But that doesnt solve my problem. Infact this renders the computations slower.


Any other ideas? A User defined function to replace sumifs?
 
Weedguru13


Can you upload a file with some sample data, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Weedguru13


I don't understand the question?


It looks like you have uploaded the wrong Sample File as this has a list of A1..E4 and a series of Random Numbers, It doesn't seem to fit the question
 
Hui,

Sorry. I think i wasnt amply clear with the sample file i have used. I'll try and explain.


So i have a head "a number(say Project 1,2,3,4)" and a subhead "a character (Say Item a,b,c,d,e of the P&L account). I am trying to list all the subheads under each individual head together. So say 'a' being the revenue, i want list the revenues for all the Projects (1,2,3,& 4)together.

And the series of random numbers has been used to just feed in some data in the empty cells.

I know this sounds simple, and can be achieved using sumifs, sumproduct, or sum with if, but sumifs is not an option in 2003 and the other two slow down excel ( i have a huge number of formulas using sumifs.


I hope this helps.
 
F22:

=INDEX($F$4:$T$19,MAX((ROW($C$4:$C$19)-ROW($C$3))*($C$4:$C$19=$C22)*($D$4:$D$19=$D22)),COLUMN()-COLUMN($E$1)) Ctrl+Shift+Enter


Copy across

Then copy down


If there is always going to be 4 Expense types and 4 projects there is a simpler way, But I doubt that is the case
 
Sheep camel s hair (ALPACA), also clongchamp purses named camel wool , fiber for 20 to 40 centimeters, white, brown, grey, black colour including colour, for 90% manufactured in Peru, also known as Peru wool. its two sorts, one particular variety is fiber curly, with silver sheen, and also the other will be the fiber with curly significantly less, the existence in the approximate mohair luster, frequently with other fiber blended, as making high-quality clothing high-quality material. Currently out there camel wool, a lot of the goods is japanese Europe.


Four, rabbit hair


Rabbit hair to gentle, fantastic, delicate, warmth retention house is powerful, the qualities longchamp messenger bag of the rate affordable and well-known. It can be designed longchamp messenger bag of soft villi and kemp of composition, most important have normally rabbit and also the angolan rabbit hair, and also the superior on the optimal. Rabbit hair and wool fiber difference is slender, particular surface easy, easy identification. longchamp Simply because rabbit hair of lower intensity, instead of very easily spinning alone, so many and wool or other fiber blended, making into knitwear and woman? Coat cloth? And so forth.


The strategy of pure wool and logos


Out there, it's typical to determine wool goods are pure wool or 100% wool two tags, a number of people think that pure wool which is 100% wool , true not. Basically pure wool should be 100% wool. But in reality, inside the means of generate, to boost the efficiency with the textile fiber, make the fabric a lot more tough, some merchandise often desire to be part of some polyester fiber or the hair fibers. To join quantity of exactly how much, the dominion of scale that defined. So, let s be Longchamp Backpack apparent about. Pure wool products isn't 100% wool, present that pure wool solutions, is presently in accordance with the provisions from the classification of wool fibers, and for that reason should be over 100% wool product price tag is minimal.


In short, in an more and more prosperous current market with the cheap longchamp purses ideal acquire wool textiles, apart from we see, touch, question application, compared to the actions for instance identification, analysis from value can very long. Not surprisingly, the insurance coverage continues to be to wish to add towards the products know-how always comprehend.


Establish longchamp messenger bag the cashmere, sheep camel s hair, a single


Tags: authentic longchamp bags, low-cost longchamp purses, longchamp outlet
 
Oops. Another issue. Seems like it works fine with one instance of a head, but with two of the same kind it doesn't seem to replicate Sumifs. Please have a look.


Have attached the sample file.


http://speedy.sh/bcmqH/Copy-of-Sample-File.xlsx
 
You must log in or register to reply here.
Back
Top