Sumif based on multiple Criteria

[mnuttall87]

Hello again Excel users,


Quick question here. I'm thinking the solution is a sumproduct, but I'm not entirely sure.


I want to use a sumif formula, that for the [range] portion of the sum I can calculate it based on multiple columns. For example, sumif(my range,investor name,IF column = "other" then sum it) would be the synxtax I am looking for.


Any ideas suggestions?


Thanks,

-Matt

 

[Hui]

Matt


How about posting an example file for us to review?

 

[mnuttall87]

Sure thing. Here is a link to the workbook (Office 2010 format)


https://docs.google.com/open?id=0B6AHVGZgtI6qbWN3cXgzZHZoenc


So with this sample criteria, the "Multiple" sumif is based on a criteria in Column A, but also by Matching a Row titled "Other" (or some other word) in Row 1.

 

[Montrey]

Here ya go.


=SUMPRODUCT((a1=a1)*(J1:O1=J1)*(J2:O2))


A1 should be the investor name, J1:O1 should be your titles row which include "Other",

J2:O2 should be your values.


Enter with Ctrl+shift+Enter.


Basically it is

=sumproduct((Investornamerange=investorname)*(Titlerange=other)*(valuerange))

 

[mnuttall87]

Okay, so I tried the following formula in cell C16 of the sample workbook that I posted and I'm coming up with N/A. I will keep playing with this. (I did ctrl+Shift+enter for the array formula).


=SUMPRODUCT(($A$1:$A$8=A16)*($A$1:$H$1="Other")*($B$2:$H$8))


I've read various how-to's related to sumproduct but I think I might just be missing something still.

 

[Luke M]

Your arrays aren't the same size, and it's causing the error. First array includes 8 rows, but your value array only has 7. Adjusting formula:


=SUMPRODUCT(($A$2:$A$8=A16)*($B$1:$H$1="Other")*($B$2:$H$8))

 

[mnuttall87]

Perfect. Thanks Luke/Montrey. Sumproduct is definitely a formula I am trying to figure out and get a grasp on. Appreciate the input.

 

[Montrey]

Glad we could help. Sumproduct is very powerful. Love it.