Formula for intersection of rows and columns

Welcome to the forum.


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

First, you will need to unmerge cells.

Your first requirement needs this:

=FILTER(A2:A25,B2:B25)

Your second requirement is unclear to me. Please manually fill in your expected results in the section starting in green so that we can test possible solutions.

There are no new comments. I had already read the comments that were there and asked you to clarify them. You haven't done so.

Please explain the results that you are showing - where do they come from and why?

You will still need to get rid of merged cells.

I already gave you a formula for the first issue:

=FILTER(A2:A25,B2:B25)

See the workbook attached to post #4.

We got 5 because Analytics intersects with five modules, each of which has 1 at the intersection.

Where?

Analytics seems to intersect with all modules:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	G	H
1	Modules(columns)	Analytics
2	d3	1
3	Administration	1
4	Obstetrics	1
5	Pharmacy	1
6	BARS - Healthcare Monitoring	1
7	BARS.STAR	1
8	Vaccination	1
9	VIMIS RF AKiNEO	1
10	VIMIS RF Oncology	1
11	VIMIS RF Prevention	1
12	VIMIS RF CVD	1
13	HMP	1
14	Outside ARM	1
15	Military registration	1
16	Hospital physician	1
17	Diagnostics	1
18	Dispensary registration	1
19	Remote monitoring	1
20	Health diary	1
21	Unified registry	1
22	User information support	1
23	IPRA	1
24	IEMC	1
25	Call center	1

Sheet: Sheet1

OK - worked it out.

=FILTER(D18:D24,E18:E24)

=LET(a,SUM(FILTER(FILTER($H$2:$N$25,$H$1:$N$1=D4),$B$2:$B$25))*3,a*1+TEXTAFTER($E$15,"-"))

Spill version:

=BYROW(D4#,LAMBDA(r,IFERROR(LET(a,SUM(FILTER(FILTER($H$2:$N$25,$H$1:$N$1=r),$B$2:$B$25))*3,a*1+TEXTAFTER($E$15,"-")),"")))

All-in-ine solution:

Please Login or Register  to view this content.

Have you looked at the ultimate solution in post #11?

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.

All these actions should be performed by one formula, there cannot be several.

There is only one formula in post #11. It spills all of the results into the cells to the right and below. The formula is in D4. If you delete it, all of the results will go with it.

Just to clarify, this is ONE FORMULA:

Please Login or Register  to view this content.

It goes into D4. Open the workbook attached to see it working.

No, there is not.

What is wrong with the formula? It works exactly the same as a function would do.

Slight change to the formula:

Please Login or Register  to view this content.

when I change any value, it immediately writes an error #NAME?

Your forum profile says that you are using 365, so the formula has been built for that version of Excel. Is your 365 completely up-to-date? Or are you using something else?

Well, you should have mentioned that in post #1.

Sorry, my formula was built for 365 - it won't work in thhe version you have.

It can be done in your version, but NOT in oine formula.

For 2021

In D4:

=FILTER(D18:D24,E18:E24)

In E4 copied down:

=IFERROR(LET(a,SUM(FILTER(FILTER($H$2:$N$25,$H$1:$N$1=D4),$B$2:$B$25))*3,a*(1+MID($E$15,FIND("-",$E$15)+1,4))),"")

Ok, will this formula work with google sheets?

Not without tweaking, I suspect. I've given you a 2021 solution.

Try it yourself in GoogleSheets.

Not in Excel 2021, no. Please read what I am telling you carefully.

This will work in D4 in GoogleSheets:

Please Login or Register  to view this content.

I have now solved this for you three times.

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.

Originally Posted by Extend

Is it possible for all of this to be in cell D4?

What to be in a single cell?
Do you mean all text string ( 4 cells, in this sample) combination in a cell?

No, they mean one single spill formula, which I have provided for both 365 (post #20) and GoogleSheets (post #26) above.

For such a complex requirement, if you want it to be usable in Excel 2007+, you should consider using a UDF (User Defined Function) in D4 and copying it to the entire range.

You're welcome.

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.

A very (maybe too?) simple approach: Write into AliGW's file of post #20
F26: 10%
F27: 25%
F28: 50%
G26: Easy
G27: Medium
G28: Hard
H26: =3*SUMPRODUCT(--$B$2:$B$25,H$2:H$25)*(1+$F26)
and copy H26 to H26:N28.
To make it easy for future extensions you can move the three new rows up: select rows 26:28, press CTRL + X and insert these rows on top.
This should work for all Excel versions, Google sheet, Lotus1-2-3, and maybe even Visicalc :-)