Find values that match many criteria including several column headers

The Problem.jpgHello everyone, I have tried applying all of my (admittedly not huge) knowledge in various methods to this problem; including attempting various combinations of array formulas, COUNTIFS, SUMPRODUCT etc.... all to no avail!

Essentially what I am attempting to do (in a formula at the moment) is as follows:

1)

• Find all values vertically from Column F and look at all rows that match (specific location),
• Look at specific headers horizontally in row 8 (columns Q:IQ) with the text OSA only (multiple will apply),
• Then in ALL the columns that meet the header criteria, and match have a match in column F, count the number of values that fall between two dates (i.e. >= Today - five years AND <= Today)

2)

• I would also like to find the total of items that then fall within the dates above and outside of these dates above (or basically any date) and have either an "m", "p" or "x" but ignore cells that have "N/A" and are blank!

Then I would like to display them in a separate table.

I hope this makes sense and that I am not asking too much, please see attached example of my dataset which I hope will help...

Cheers,

James

Originally Posted by Richard Buttrey

Hello James and welcome to the forum.

Almost without exception we prefer you to upload the actual workbook so that we have something to work with. Pictures are rarely much use in cases like this.
Before you upload the workbook manually enter the results you wish to see in whatever table or range you want the results so that we know the end goal.

Hi Richard,sorry i could not get the attachments option to work before. I have uploaded from my mobile.

Cheers,

James

Just as an update, I have had a solution for this problem from NBVC over at Ozgrid (I hope it is ok to give credit to someone on another website).

Anyway, here is the answer that works / provides a solution to this question:

Try Sumproduct...

e.g.

=SUMPRODUCT(($Q$9:$IQ$27>=TODAY()-365*5)*($Q$9:$IQ$27<=TODAY())*($F$9:$F$27=$F32)*($Q$8:$IQ$8="OSA"))

copied down

and for the 2nd part...

=SUMPRODUCT((($Q$9:$IQ$27>=TODAY()-365*5)*($Q$9:$IQ$27<=TODAY())+(ISNUMBER(MATCH($Q$9:$IQ$27,{"m","p","x"},0))))*($F$9:$F$27=$F32)*($Q$8:$IQ$8="OSA"))

Regards,

James