SUMIFS Substitute

[Ekac]

Hi,

I have been using

Please Login or Register  to view this content.

for a while.I have to copy and paste below in most cases.What I am expecting that I want to spill the data by putting the formula in a single cell.How this can be done.

[AliGW]

You'll probably need BYROW or MAP with a LAMBDA.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[AliGW]

Follow this example for C1 (Excel 365 only):

=MAP(A3:A5,B3:B5,LAMBDA(x,y,SUMPRODUCT(H3:H8*(F3:F8=x)*(G3:G8=y))))

x and y refer to the two mapped ranges in the order they appear in the formula.

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C	D	E	F	G	H
3	a	1	341			a	1	280
4	b	2	324			b	2	87
5	c	3	251			c	3	202
6						a	1	61
7						b	2	237
8						c	3	49

Sheet: Sheet1

[Ekac]

Ok based on your formula,I have used

Please Login or Register  to view this content.

as there is a #VALUE!.I think something is missing.

[AliGW]

Yet you have failed to provide a workbook ... How do you expect me to troubleshoot it???

It sounds like you have a data mismatch of some type, but without seeing it in situ, it's imposible to say.

Nothing is missing, as you can see from the example I created.

[AliGW]

For the avoidance of doubt, I am waiting to see your workbook.

[Ekac]

Ok I have attached a file.I want in the result in Sheet1 at the place which I have highlighted with yellow which I required from Sheet2.

[AliGW]

Working, although may not be accurate:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	Z	AA
78	Country X	Bolly	256	=MAP(A78:A88,B78:B88,LAMBDA(x,y,SUMPRODUCT(Sheet2!I10:I19*(Sheet2!N10:N19=x)*(Sheet2!B10:B19=y))))
79	Country X	Pumpkin	984
80	Country X	Chicken	9582
81	Country X	Indiana Jone	654
82	Country X	Apple	50263
83	Country X	Mango	3.136
84	Country X	Tumbaktu	0
85	Country X	Rhodoendron	25
86	Country X	Worldcup	3310
87	Country X	Pure	3575
88	Country X		0

Sheet: Sheet1

[AliGW]

Here's your file with both spill formulae installed.

[Ekac]

Its not working.I have extended the range below but throwing '0's.Plz see the file attached for your kind reference.

[Ekac]

I have now seen your post #9.But I want a single formula with range I have mentioned in Sheet1>A78:A104 & B78:B104 and spill automatically and those that are blank in A & B or unmatched with leave blank.This is so becoz I have a lot of Country as I have mentioned only few country X & Country Y for demo purpose so every now then copy paste would be tedious work.

[AliGW]

Of course it won't work like that because of the data layout on both sheets, which complicates matters.

Try this:

Please Login or Register  to view this content.

The more complicated your layout, the more complex the formula. Always try to work with normalised data layouts.

[Ekac]

Country X Total & Country Y Total was not found in Sheet1.This seems missing.

[AliGW]

You failed to provide a sample workbook in post 1. When you everntually did, there were NO expected results mocked up. And your posts are demanding in tone. I am having to GUESS what you need. This is unacceptable - in future, please provide all of these details up front.

I will have another look.

[AliGW]

Remove the XXXXXX from the Total rows in column B, then this:

Please Login or Register  to view this content.

[Ekac]

I got my mistakes where I was wrong in presenting. Temporarily I have used Z1 as reference inorder for better understanding.Can you use ChooseCol function for Sheet2!D8:L8=Z1 something like Choose Col 9.

[AliGW]

Why? It's much better to have a reference cell.

You could do this:

Please Login or Register  to view this content.

which is easier to edit than a CHOOSECOLS clause.

I suspect now that the results sheet in your sample workbook is not the full story, however a reference cell is MUCH easier to update than fiddling with the formula, which should be left as it is.

Yet another bit of information you didn't give me at the start!!! When will it end?

[AliGW]

I've just been looking back through your recent threads, and the things missing from all of them are two little words: "thank you". Please remember that we are not working for you: we are giving of our time for free and out of goodwill.

You have had enough guidance in this thread to make it work with CHOOSECOLS if you prefer - you seem to have a strange obsession with this function, so must know how it works. I don't believe it's the optimum option here, though.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[Ekac]

Ok I have been using all of your approaches in my real workbook and it seems doing fine.

Thanx for your kind help

[AliGW]

Please mark the thread as solved as mentioned in my last post.

You're welcome.