asked on

Formula Help - Lookup? Index? Match?

Hello Experts,

I have a table of data, that I'm trying to create a summary of. I know I can easily do this via a Pivot Table, but I do want to accomplish this via formulas.

Please via attached screenshot.

This table includes a summary of all ticket types (codes).

I will have another worksheet, that will be the summary page.

Here is an example of the summary page, do NOT pay attention to the "1" values - as I only created this pivot to illustrate the layout.

Codes along the left side.
Months along the top.

I would like a formula that will return the median value, if it matches the code & the month.

I hope that made sense.

Thank you in advance for your help!

~ Geekamo

Geekamo

ASKER

And btw, I'm praying the solution to this - DOES NOT involve array formulas...? :)

NBVC

Assuming summary row/column headers in positions shown,

Try:

=SUMIFS(MF!C:C,MF!A:A,$A10,MF!F:F,B$4)

you may have to format to match format of MF column C

Geekamo

ASKER

@ NB_VC,

Your formula appears to be working perfectly.

I have edited it to fit my setup. So I am now using,...

=SUMIFS(Median,Code,$A5,Month,B$4)

Some values are not present, so it was turning zero's. So to avoid the zeros I customized the formula even further. So I am now using,...

=IF(SUMIFS(Median,Code,$A5,Month,B$4)=0,"",SUMIFS(Median,Code,$A5,Month,B$4))

Based on the revisions I've made to the formula, do you see any issues? I mean it is working perfectly. But idk if that's the best way to customize the formula. Any suggestions at this point?

Thanks,
~ Geekamo

ASKER CERTIFIED SOLUTION

NBVC

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Geekamo

ASKER

@ NB_VC,

That is a great solution! Thank you so much.

~ Geekamo