COUNTIF/SUMPRODUCT(?) to create a matrix from data

A picture is worth 1000 words, a sample sheet is worth 1000 pictures!!

You have attached a non-editable picture of an Excel sheet. That's not very easy to work with. Also... I'm lazy. I have to re-type your information before I can begin to address your problem. That puts me off completely.

So.... Do yourself a favour and please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough). However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever).

Unfortunately the attachment icon doesn't work at the moment. So,... instead click on Go Advanced (below the Edit Window) while composing your reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Make sure confidential information is removed first!!!!

Welcome to the forum.

Please read the yellow banner at the top of the page.

This looks horrible. It is horrible. But... it does seem to work... I couldn't think of a prettier way to do it!!

Someone will do it with a punchy one-line formula, and put me to shame!!!

Formula:

=IFERROR(SUMPRODUCT(

(INDEX(data!$F$6:$P$6,,MATCH(output!$D6,data!$F$5:$P$5,0)):INDEX(data!$F$23:$P$23,,MATCH(output!$D6,data!$F$5:$P$5,0))<>"")*

(INDEX(data!$F$6:$P$6,,MATCH(output!$D6,data!$F$5:$P$5,0)-1):INDEX(data!$F$23:$P$23,,MATCH(output!$D6,data!$F$5:$P$5,0)-1)<>

INDEX(data!$F$6:$P$6,,MATCH(output!$D6,data!$F$5:$P$5,0)):INDEX(data!$F$23:$P$23,,MATCH(output!$D6,data!$F$5:$P$5,0)))*

(INDEX(data!$F$6:$P$6,,MATCH(output!E$5,data!$F$5:$P$5,0)):INDEX(data!$F$23:$P$23,,MATCH(output!E$5,data!$F$5:$P$5,0))<>"")*

(INDEX(data!$F$6:$P$6,,MATCH(output!E$5,data!$F$5:$P$5,0)+1):INDEX(data!$F$23:$P$23,,MATCH(output!E$5,data!$F$5:$P$5,0)+1)<>

INDEX(data!$F$6:$P$6,,MATCH(output!E$5,data!$F$5:$P$5,0)):INDEX(data!$F$23:$P$23,,MATCH(output!E$5,data!$F$5:$P$5,0)))),"")

I was trying MMULT - not managed it yet.

MMULT still scares me a bit!!

It shouldn't be too difficult.

There are only 3 ranges in the formula:
Header row
First data row
Last data row.

The main thing is... is it working on your sample as desired?

If so, you're welcome. If not, shout.

---

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

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

It's just about working in my sample thanks, but there are a couple of odd results. For example, the matrix shows four vehicles arriving at 0830 and leaving at 0915, whereas only three do (SD19, J333 and SL61) and it shows one vehicle arriving at 0915 but departing at 0900.

I'm away now. Beer o'clock. Back tomorrow.

OK. I set up 6 Named ranges to make the formula look simpler. CTRL-F3 to view edit. They select the range of the table for each action needed in SUMPRODUCT.

They are all based on the use of INDEX;MATCH to select the column of the arrival time the column to the left of it and the column to the right of it (A_Rng0, A_RngMinus and A_RngPlus) and similarly for the departure time. The SUMPRODUCT then looks like this:

=IFERROR(IF(E$5<$D6,"",SUMPRODUCT((A_Rng0<>"")*(A_Rng0<>A_RngMinus)*(D_Rng0<>"")*(D_Rng0<>D_RngPlus))),"")

REMEMBER the Named Ranges MUST extend ONE column to the RIGHT of the last column with data.

N.B. You said: "the matrix shows four vehicles arriving at 0830 and leaving at 0915, whereas only three do (SD19, J333 and SL61)". In fact there are four. You forgot to count SH66.

Back in an hour.

Back again.

In fact, you have done nothing wrong. Strange things can happen when copy/pasting times. It's due either to time being expressed to a different number of dps or to the fact that excel can't count. (See link). It can be fixed int he formual using some clunky rounding. However, since (in this case) all 3 time axes were identical, I just selected the time axis in your raw data copy/paste special ± transpose into your output. As if by magic, all the blanks disappearred.

Hopefully, that's everything sorted.

If so, you're welcome. If not, shout.

---

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

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Ooops. Your file this time.

Oh, here's the link, as well....

https://support.microsoft.com/en-gb/...sults-in-excel

Eh? There are no 55's on the sheet I posted @ 16??!!

I see them now!! Give me 5 mins.

That was me being monstrously stupid!!!

Check it over. The problem was that I foolishly forgot to extend the raw data raneg BACK one column as well as FORWARD one column. All the NR's are (hopefully) OK now.

Apols. Must be going senile.

Grrr.

Normally, I overcomplicate things. This time, I undercomplicated the problem. Once I saw what was causing the problem, it was easy enough to fix. (I was not checking that the vehicle departing was the same as the one that arrived - if vehicle A arrived at 0700, and left at 0800, it was counted. If another subsequently arrived and left at 1100, I had not checked that it was the same vehicle that had arrived at 0700). I deleted two of the Named ranges, as they are notrequired. So, with a red face, I have now cross-checked my formula as well as I can. I am now confident that we're there. It might be an idea to keep the cross-checking formulae as an ongoing QA measure.

Phew!!! That was a bit of a marathon, but we got there in the end!!