SUMIFS in array?

[TPDave]

Hi All,

Example workbook attached, I've got sales data for products broken down by month and I've got the components that make those products up. I'm trying to get a list of components used for each sales period. I've put a few SUMIFS formulas in Sheet 3, I'm looking for something to replace them that will work with hundreds of products in the Sales tab. I presume some kind of array to complete the SUMIFS on every row of the sales tab but don't really know.

Any help greatly appreciated, thanks

[Richard Buttrey]

Hi,

I don't understand your data. You show no sales for part XXXXX in month 1 yet sheet 3 indicates you need 4 of component A and 8 of component D, Furthermore according to teh BOMS sheet part XXXXX only consists of components A, B & C

It's not at all clear (at least to me) what it is you're trying to achieve. Tis is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

Remember that when you ask a question in a forum those of us who might be able to help know absolutely nothing about your system and work process or the terminology you use. Unless you have uploaded a workbook we know nothing about how it is laid out and little about what you want done with it or how the results should be presented.

Whilst these things are second nature to you since you live with the workbook all the working day we start with zero knowledge so you need to take us by the hand and explain in quite precise detail your aims and goals as well as stuff like how your data might look different in other situations.
Don't assume we are able to "figure it out". We're usually quite good but not prescient.

Put yourself in our position and think about whether what you've posted makes sense and is unambiguous to someone who's never seen it before.
You are asking us for help so help us to be able to help you by providing all the information we need, even if that information seems obvious or intuitive to you.

[TPDave]

Hi Richard,

Thanks for taking the time to look at this for me, sorry if I haven't been clear in my explanation. Looking back at my example I can see that I have made an error in the formula - apologies. I've attached a corrected sheet for reference.

You're right that there are no sales of XXXXX in month 1 however there are 2 sales of YYYYY which contains 2 of component A. Component A is not in ZZZZZ so the monthly total required for Component A is 4 (0 for sales of XXXXX, 4 for sales of YYYYY and 0 for sales of ZZZZZ). I hope the corrected sheet is clearer!

I can try to cleanse the full dataset I'm working with, my understanding was that example sheets are supposed to be smaller and simplified versions of the problem at hand. I can assure you that the mechanics are the same though so hopefully the corrected example sheet would be workable?

Thanks

[Glenn Kennedy]

I was just about to post... and tell you that your expected answers were wrong... when you posted and CONFIRMED that your expected answers were wrong!!


=SUMPRODUCT(--(BOMS!$B$2:$B$12=$A2)*BOMS!$C$2:$C$12,MMULT(--(BOMS!$A$2:$A$12=TRANSPOSE(Sales!$A$2:$A$4)),Sales!B$2:B$4))

in sheet 3, B2, copied across and dnown.

[Glenn Kennedy]

Regarding sample sheets. No-one will look atr thousands of rows... at least no-one in their right mind will... as manual checking is impossible. However it IS important that expected answers are correct correct expected answers are far more important than completely non-working formulae. in your case (2nd time round) the formulae were working.. just cumbersome.

I forgot to add:

You seem to be using an older version of Excel than me. So, please refer to the attached file. If the formulae are enclosed within a pair of { }, these are array formulae.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Bo_Ry]

Please try at B2

=SUMPRODUCT(SUMIFS(Sales!B$2:B$4,Sales!$A$2:$A$4,BOMS!$A$2:$A$12),--(BOMS!$B$2:$B$12=$A2),BOMS!$C$2:$C$12)

[TPDave]

That's perfect, thanks both