Lookup month and calculate if specific month value apply

[D-smoke]

Hi guys,
I've got a bit of a problem. I'm using a sheet to calculate cost based on hours, hourly cost and a few other values.

What I aim to do is to create a formula that checks if D5:BD5 has a certain month value, only specificFormula based on months.xlsx columns included in that month should be included in the calculation. So for example,

The formula in D23 should have something that says that the columns D5:BD5 that are "january" should be the only ones used in the calculation. So some sort of IF or LOOKUP function I guess?

Any ideas?

[Jonmo1]

If you make row 4 (E4:BD4) not have any blanks, put the month name in each and every cell..
I.E...
JAN JAN JAN FEB FEB FEB etc..
insted of
JAN blank blank Feb Blank Blank etc..

Then you can use this in D23 and filled down/right
=SUMPRODUCT(($C$9:$C$12=$B23)*($B$9:$B$12)*($E$4:$BD$4=D$20)*$E$9:$BD$12)

[benishiryo]

hi D-Smoke, a variation of Jonmo's formula:
=SUMPRODUCT(($C$9:$C$12=$B23)*($B$9:$B$12)*(TEXT($E$5:$BD$5,"mmm")=D$20)*$E$9:$BD$12)

don't have to worry about blanks

[Jonmo1]

hi D-Smoke, a variation of Jonmo's formula:
=SUMPRODUCT(($C$9:$C$12=$B23)*($B$9:$B$12)*(TEXT($E$5:$BD$5,"mmm")=D$20)*$E$9:$BD$12)

don't have to worry about blanks

Nice, I didn't even notice those were dates in row 5. They looked like just random numbers to me

[benishiryo]

@Jonmo:

yeah i noticed a formula there & checked it out. simply took the convenient way of adapting your formula. =)

[D-smoke]

Hi,
Thanks to the both of you for your kind help. I used benishiryo's formula since it refers to the dates in row 5. When I apply the formula to the sheet I attached it works fine, but when I try it on my original file I get a "VALUE" error. I've tried to find out why but can't get my head around it.

Perhaps it's hard to see an error if I just paste it here, but if you see something that is incorrect please let me know.

=SUMPRODUCT('Budget (w. plan)'!D10:D20=B39)*('Budget (w. plan)'!C10:C19)*(TEXT('Budget (w. plan)'!E6:BE6;"mmm")=D36)*'Budget (w. plan)'!E10:BE19

Thanks!

[Ace_XL]

Use consistenet ranges, hence

=SUMPRODUCT('Budget (w. plan)'!D10:D19=B39)*('Budget (w. plan)'!C10:C19)*(TEXT('Budget (w. plan)'!E6:BE6;"mmm")=D36)*'Budget (w. plan)'!E10:BE19

[D-smoke]

Hi ACE_XL,
I changed the ranges as you suggest, but I still get the "VALUE" error? Any thoughts of why this could be?

Cheers!

[D-smoke]

Hi!

I finally managed to find the error my self. It was the placement of the parenthesis. So thanks guys for your great help! I will add to your rep!
Cheers!