sumproduct, 2 variables in 2 columns, is it possible?

**Bucco63** · 03-05-2010, 08:07 PM

I've attached a sample with a description of what I'm trying to accomplish.

I need to find data in 2 columns with the same heading that meet certain criteria in the row, and of course sum the total.

Please look at attachment and I think that will be more easily understood.

Thanks/

**Huron** · 03-05-2010, 08:36 PM

You can use Sumproduct in your example

=SUMPRODUCT(($B$2:$L$2=C15)*($A$4:$A$11="XY")*($B$4:$L$11))

**Bucco63** · 03-05-2010, 08:55 PM

Sweet! Thank you!

**Bucco63** · 03-08-2010, 02:51 PM

Now what if I want to instead of being equal to FY10-6 or whatver, instead want it to add if row 2 "contains" FY10?

Is that possible?

**XyosExcel** · 03-08-2010, 05:08 PM

there probably is ways of doing this in a single large formula but easiest way around it
is just to add an extra row to show just the FY then amend the sumproduct formula given.

See attached.

Cheers

**teylyn** · 03-08-2010, 05:28 PM

You can check if the cells contain the string like this (with "FY10" in D15)

=SUMPRODUCT((NOT(ISNA(FIND(D15,$B$2:$L$2))))*($A$4:$A$11="XY")*($B$4:$L$11))