Index/Match with multiple criteria from two tables (both columns and rows)

[tinderbox22]

Hello.
I'm attempting to do a multi-criteria lookup with data that sits in two different tables. I've attempted both an INDEX and a SUMPRODUCT option and can't seem to get the syntax right.

In basic terms, info looks like this:

TABLE 1 - MEDIA VEHICLES (C5:D8):
C5: TV
C6: CABLE
C7: SPONSORSHIP1
C8: SPONSORSHIP2
In D5:D8, I enter either 'yes' or 'no.'

TABLE 2 - DATA (F4:J16)
column F contains Months Jan through Dec, starting with F5 and down to F16
columns G through J contain the name of the medium listed in C5:C8: G4 is TV, H4 is CABLE, I4 is SPONSORSHIP1, and J4 is SPONSORSHIP2
in G5:J16 are numbers that need to be aggregated by a multi-criteria lookup.

So, I'm looking to create a new table where cells B2:B13 look up values based on the month and whether or not there is a "yes" listed in the media section.
My new table will look like this:

A2:A13 = months
B2:B13 = totals

How do I get the INDEX to read two tables like this? In other words, I'm struggling with, if, for example, any cell in D5:D8 contain a 'yes,' then match it to the cells in G4:J4 and return the data in those columns. If a cell in D5:D8 contain a 'no,' then don't read that corresponding column in G4:J4.

Let me know if this does not make sense.
Thank you.

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1
2	Jan	13
3	Feb	20
4	Mar	8					TV	CABLE	SPONSORSHIP1	SPONSORSHIP2
5	Apr	21	TV	yes		Jan	5	2	6	8
6	May	17	CABLE	yes		Feb	10	8	2	9
7	Jun	20	SPONSORSHIP1	yes		Mar	1	6	1	6
8	Jul	14	SPONSORSHIP2	no		Apr	10	9	2	1
9	Aug	6				May	10	3	4	9
10	Sep	13				Jun	6	5	9	6
11	Oct	15				Jul	4	6	4	4
12	Nov	20				Aug	2	2	2	4
13	Dec	22				Sep	3	7	3	9
14						Oct	3	10	2	2
15						Nov	8	3	9	1
16						Dec	3	9	10	3
Sheet2

Cell Formulas
Range	Formula
B2:B13	B2	=MMULT(G5:J5,--($D$5:$D$8="yes"))

You can also use SUMPRODUCT like this:

=SUMPRODUCT(--(TRANSPOSE($D$5:$D$8)="yes"),G5:J5)

but it requires Control+Shift+Enter, and is longer regardless.

 

[tinderbox22]

So, the MMULT returned a VALUE error since some of my data cells have no data in them. Is there a way to squeeze an IFERROR statement within this function to not count the errors?

The SUMPRODUCT function worked though, so for now I can use that.

 

[Eric W]

Right, the SUMPRODUCT treats anything non-numeric as a 0, while the MMULT throws an error. You can convert a space to a 0 by adding a 0 to it, so your B2 formula would just be:

=MMULT(G5:J5+0,--($D$5:$D$8="yes"))

If there are actual text values in your range, this won't work.

 

[tinderbox22]

That worked. The +0 is the answer to the MMULT issue. Thanks again for all the help!

 

[Eric W]

Happy to help!