Sumproduct for multiple criteria across columns and rows

Hi,

I've not used SUMPRODUCT previously and can't understand how to get results for the attached. I'm hoping someone will find this pretty easy to solve!

I've tried SUMIFS but it doesn't work because I'm looking down columns and across rows, I'm assuming.

I've attached a summary of what I'm trying to achieve. I want to sum all costs with an R,P,I,G, etc. in column C for December '13 (E3) in the top table.

The second table is actually in a different sheet but is the source of the data I need added.

Any help would be greatly appreciated!

Many thanks
Stuart

Hi benishiryo,

That gives the result I'm after.

I'm just working through understanding the basis of the formula so I can use it in the future, if needed.

Thank you very much for your quick response

OK, now I realise I also need to sum to the date in E3 based on the above assumptions. e.g. R will equal 2,345, P will equal 2,395 and will need this to change dependant on the date in E3.

TIA

Thank you reimar-rem that works too

Originally Posted by reimar_rem

hI



If you want E3 to be dependent used this in E7:E10

=SUMPRODUCT(($C$20:$C$24=$C7)*($G$17:$R$17<=$E$3)*$G$20:$R$24)


If this post helped please click ADD REPUTATION left below of this post

If you want E3 to be dependent used this in E7:E10

=SUMPRODUCT(($C$20:$C$24=$C7)*($G$17:$R$17<=$E$3)*$G$20:$R$24)


If this post helped please click ADD REPUTATION left below of this post[/QUOTE]

Thanks for this, I managed to get to that stage myself - luckily!!

I have a problem though when transferring to my actual sheets. When I change the formula to suit the different source sheet, I can only reference from A11:A190 when I need to get to A288. Is there a limit on the number of rows which can be used? I keep getting a value error. I'm keeping the lookup range and data range the same to the same row numbers, e.g. $C$11:$C$288 and $G$11:$R$288
and the date range lookup is $G$10:$R$10.

I hope that makes sense?

Thanks

Apologies for the delay. File attached.

Solved the problem. The referencing cells had formulae that was pointing to blank cells, therefore no value was coming through and the SUMPRODUCT formula wouldn't work. All sorted now thanks.