sum if some columns if both 2 vertical references and 1 horizontal reference is there

Dares2

New Member
Hi
I am wanting to sum a group of columns on one sheet if the vertical value is 1. not blank and 2. equals a certain value (like a vlookup) on the criteria sheet and 3 looks at a row on criteria sheet if this cell is "TRUE" it adds this column with the rest. This is to achieve adding all the months in a budget range if criteria is met.
I did have a sumproduct function =SUM(IF(B9<>"",SUMPRODUCT(('P&L Act'!\$B\$13:\$B\$163=B9)*(Actuals)))) but this is adding all the columns instead of just some columns if the horizontal range is TRUE?
Thanks

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Are you able to give examples of what is in the columns, or exactly where the columns are?

And what the "Actuals" consist of...

Are you able to give examples of what is in the columns, or exactly where the columns are?

And what the "Actuals" consist of...

Thankyou I was able to solve both horizontal and vertical lookups with a sumproduct formula in the end

=IF(B24="","",SUMPRODUCT(('P&L Budget'!\$B\$12:\$B\$163='P&L Detail'!B24)*('P&L Budget'!\$D\$11:\$O\$11=\$R\$2)*('P&L Budget'!\$D\$12:\$O\$163)))

Either way thanks for the reply. I love this site!

Replies
5
Views
201
Replies
0
Views
170
Replies
19
Views
655
Replies
9
Views
519
Replies
3
Views
908

1,203,096
Messages
6,053,512
Members
444,669
Latest member
Renarian

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back