INDEX matching multiple criteria with multiple results

[Montanes]

Please help,

In the first tab "Input" i have data as follows:

A=Serial Number
B=Date
C=Client
D=Supplier
E=Mileage
F=Cost
G=Vehicle

this rows of data are entered over a yearly period.

In another tab, i wish to summarise this information and print the results including total figures.

I expect the information of any month to print on to A4 paper so at the bottom og the page i would include =Sum formula to total the column.

If for example i want to list all the data for a certain vehicle in a certain month what formula would i use?

I can so far match the vehicle, but struggling with the month part.

here is the array formula i am currently using:

{=IF(ISERROR(INDEX(INPUT!$A$1:$G$500,SMALL(IF(INPUT!$A$1:$G$500=$B$3,ROW(INPUT!$A$1:$G$500)),ROW(1:1)),2))."",INDEX(INPUT!$A$1:$G$500,SMALL(IF(INPUT!$A$1:$G$500=$B$3,ROW(INPUT!$A$1:$G$500)),ROW(1:1)),2))}

I copied that formula, i dont understand it though. Basically it returns all the information in various rows that matches the vehicle reg i have in B3. I now need it to only return the information from a certain month that i may enter in E3.

I hope i have explained this properly. Any help is greatly appreciated.

[JBeaucaire]

Personally, I would add a "helper" column to my data set so that as I keep editing my criteria on the REPORT page a numeric "index" would appear in the data so the main report didn't need to do all the array stuff you're doing.

If you post you your workbook so I don't have to create it manually I'll show you exactly.

In general, in column H you'll create a "test" formula that looks to see if the ONE row matches the criteria on the report, and if so it adds a number. The formula adds a new number for each row that uniquely matches the criteria, whatever it may be.

Then you do a simple INDEX/MATCH on the REPORT sheet to show all the uniquely numbered rows. No array.


Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

[Montanes]

In tab "Input" i enter the relevant data on going.

In tab "Summary" i would like to print A4 the results matching Reg No and Month as indicated in B3 & E3 consecutively.

The result would then be shown from row 7 down (example in red). i do not expect any more than 20 rows per month, so i intend to place simple sums at the foot of the page to total the returned information.

Thanks in advance if anyone comes up with an answer, it's driving me insane!!

[Haseeb Avarakkan]

Hello Montanes,

See the attached. Used a dynamic range to adjust the data accordingly. Hope this helps.

[Montanes]

I dont know how, but it works. Thanks a lot.