Sumifs on multiple columns

[Matt_Chelmsford]

Hello there,

I need help on a formula.

On my spreadsheet B1 to F5 I have Mon to Fri
In column A I could have Bruce, Roy, Steve, Nils, Clarence, Max and Danny at any time and repeating, and in the body I have monetary values.

I want to have two drop down columns in Cells J1 and J2. J1 for mon to Fri and J2 For the names.

I need a formula that in J4 that will add up the values for a particular person on a given day.

I guess I need the principles of a sumif that can look at multiple columns.

I would be grateful of any help you can give.

Kind Regards

Matthew

 

[jasonb75]

That could be done with sumif rather than sumifs, see this thread for a couple of methods.

http://www.mrexcel.com/forum/showthread.php?t=544548

(barry's suggestion is the better of them).

 

[AlphaFrog]

Put this in J4

=SUMIF(A:A, J2, INDEX(B:F,,MATCH(J1,B1:F1,0)))

 

[Matt_Chelmsford]

That does the job nicely - thank you very much!