Formula needed for a matrix

[KatOpti]

Hi everyone, this is my first post and I hope someone can help me.

I need to automate the task of filling out a matrix which counts for each country how many calls they have been on with every other country for just 2 funding frameworks. Using filters I have manually counted for Austria the number of calls they have had with Belgium and Bulgaria to demonstrate. However, to do this manually for all 28 countries will take too long so I need a formula that will use the variables in the following columns:

Column U: “ERA-NET Cofund” & “ERA-NET Cofund (unfunded call)”
Column Z: 1 (some countries have more than one funding organisation in a call so they have been numbered 1, 2, 3 etc)
Column Y: 2 countries eg Austria & Belgium
Column M: Only count Joint Calls for both countries

I have tried using COUNTIFS but it returns 0 when there is more than 1 criteria to be counted in the same column (U and Y). The other problem I have is with column M, I only want to count the Joint Calls for both countries but I don’t know how to count duplicates only or to ignore unique.

I have attached a sample of my Masterfile to show what the data looks like.

Thanks

[AliGW]

Not sure I completely understand this:

Column Y: 2 countries eg Austria & Belgium
Column M: Only count Joint Calls for both countries

Can you tell us which 25 records count towards your first manually entered result?

[AliGW]

I can give you an answer if you will just please clarify this:

Column M: Only count Joint Calls for both countries

The other problem I have is with column M, I only want to count the Joint Calls for both countries but I don’t know how to count duplicates only or to ignore unique.

Give me some examples of what you mean by this.

[KatOpti]

Hi AliGW

I basically filtered the data on the columns listed (U, Z, Y) selecting the specific criteria.

Then for column M I deselected the Joint Calls which had only 1 of the 2 countries listed.

The result of the filtering was 50 records, however, since there is 1 record for each country on each call I divided 50 by 2 giving me 25 calls that both Austria and Belgium took part in.

Hope that explains how the manual process works and hopefully shows why I need to automate. I have the file saved with the filters still applied if you would like me to upload it.

[AliGW]

Yes - thanks. I'll have another look. Based on U, Y and Z alone I make it 56, so I need to eliminate the 6 where only one country is listed.

[KatOpti]

Exactly, I need to ignore the calls where only 1 country is listed, then divide by 2.

[AliGW]

I was hoping to be able to solve this, but I have fallen at the final hurdle.

[KatOpti]

Thanks for trying

Just hope there is someone who can help.

[JeteMc]

This isn't automatic, however I hope that it may help.
I added two helper columns to the 'data entry' sheet.
The formula that populates column BB is:

Please Login or Register  to view this content.

The formula that populates column BC is: =IF(BB694=2,1,0)
Note that I have hidden columns N:X so that I could do a count to verify my results.
On the 'Matrix' sheet cells AF2 and AG2 have data validation applied so that they drop down the names of the cities in column A.
AH2:AJ2 contain the other criteria as per listed in post #1
AL2* counts using the formula: =AGGREGATE(9,5,'Data Entry '!BC2:BC1735)/2
Unfortunately there is still a need to filter the 'data entry' sheet. Perhaps someone on the VBA side can provide a way to link the filtering process with the values in AF2:AJ2 from the 'Matrix' sheet. (However, I don't know enough about VBA to be sure that is possible)
EDIT: I'm now thinking that the filtering could be done by Advanced Filter, I'll try and work on that tomorrow (if one of the other contributors hasn't done so by then)
*AL2 is reporting a count of 24, instead of 25, for Austria & Belgium. I believe that the reason is that cells M485 and M497 are slightly different (one has a space between for and Europe and the other doesn't).
Let us know if you have any questions.

[KatOpti]

Hi JeteMc

I've tried it out a few times and it seems to deliver the results. The only trouble is the time consuming filtering as I would have to filter over 700 times to complete the matrix. So some more automation perhaps through the advanced filter will help greatly. If no-one else comes up with a solution I will definitely use this one.

I'm new to the forum so don't know - would I need to post on the VBA thread or will the guys see it in this thread?

Many thanks

[AliGW]

No - we don't allow thread duplication, but we could move the thread to the VBA section and/or add VBA to the thread title.

[KatOpti]

Thanks AliGW, I'll wait a wee while to see if anyone else comes up with a solution first.

[AliGW]

That's fine - your call!

[JeteMc]

Go ahead and move the thread if you would like. I'll still work on the Advanced filter and hopefully someone there will give you a better solution in the meantime.

[Haseeb Avarakkan]

Hello,

Try this array formula in B3 & copy down & across.

Please Login or Register  to view this content.

[KatOpti]

Hi Haseeb A, I copied your array formula into B3 and immediately got a #VALUE! error

[AliGW]

It's an array formula confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

And it looks like it works - kudos, Haseeb!

[KatOpti]

Doh! Shoulda remembered that...lol

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[KatOpti]

Hi Haseeb

I copied it into all the cells in the matrix, unfortunately from column G (Czech Republic) on it just returns zeros.

I tried copy/paste down each column and then across each row but it still stops working at column G.

Any ideas? This is a brilliant solution if it would only work beyond column G.

Cheers

[JeteMc]

The column Headers from column G and to the right are in row 2 instead of row 1 like the headers for columns B:F.
It is hard to tell because they have all be merged and centered, yet another reason to avoid merge & center.
Move those column headers up to row one, then you may then have to re-initialize the array entered formula in columns G:AC by dragging the fill handle of the cells in column F across.
Let us know if you have any questions.

[Haseeb Avarakkan]

....stops working at column G...

OK. This formula will look for header when copy across & down. Red highlighted is the change.

Please Login or Register  to view this content.

[KatOpti]

Brilliant! It's working perfectly now.

Thank you so much AliGW, JeteMc and Haseeb A, you guys are the best.

Kat

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.