# Sumif Array with horizontal and vertical criteria

#### rpm567

##### New Member
 Account Code Jan Feb Mar %Fixed %Variable 6X \$100,000 \$50,000 \$25,000 100% 0% 5X \$200,000 \$250,000 \$50,000 25% 75% 4X \$150,000 \$100,000 \$0 50% 50%

<tbody>
</tbody>

I have sheets relating to various cost centers and each line is the spend for a specific account. Let's call the above table as sheet1 and the summary table below, as sheet2. The summary table is like:

 Cost Center Fixed/Variable Jan Feb Mar 100 Fixed ? ? ? 100 Variable ? ? ?

<tbody>
</tbody>

I can't figure out the formula to fill in the cells. I have too many to do everything manually, any help would be greatly appreciated!

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### StephenCrump

##### MrExcel MVP
You haven't told us what results you're expecting?

I'm guessing that the top left "?" should be \$225,000, i.e. (100% of \$100,000) plus (25% of \$200,000) plus (50% of \$150,000)?

But how do we know that Account codes 6X, 5X and 4X belong to cost centre 100?

#### rpm567

##### New Member
You haven't told us what results you're expecting?

I'm guessing that the top left "?" should be \$225,000, i.e. (100% of \$100,000) plus (25% of \$200,000) plus (50% of \$150,000)?

But how do we know that Account codes 6X, 5X and 4X belong to cost centre 100?

Okay, sorry for being vague... let me try to frame this more appropriately.

Sheet1:

 Jan Feb Mar State %Fixed %Variable 100 500 200 NY 50% 50% 1000 2000 500 CA 100% 0% 2000 600 400 CA 0% 100% 800 1200 500 NY 75% 25%

<tbody>
</tbody>

Sheet2:
 State Fixed/Variable Costs Jan Feb Mar NY Fixed 650 1150 475 NY Variable 250 550 225 CA Fixed 1000 2000 500 CA Variable 2200 900 525

<tbody>
</tbody>

I'm looking for a formula to produce the values in red.
Thanks again for taking the time to help!

#### Tetra201

##### MrExcel MVP
Here is the formula for cell C2 on Sheet2. It must be entered using Ctrl+Shift+Enter, not just Enter. Then it can be copied down and across.

=SUM(IF(Sheet1!\$D\$2:\$D\$5=\$A2,INDEX(Sheet1!\$A\$2:\$C\$5,0,MATCH(C\$1,Sheet1!\$A\$1:\$C\$1,0)))*IF(Sheet1!\$D\$2:\$D\$5=\$A2,INDEX(Sheet1!\$E\$2:\$F\$5,0,MATCH("*"&\$B2,Sheet1!\$E\$1:\$F\$1,0))))

Note: to the best of my understanding, you've miscalculated CA Variable.

Replies
3
Views
91
Replies
3
Views
245
Replies
3
Views
298
Replies
2
Views
309
Replies
12
Views
957

1,190,919
Messages
5,983,588
Members
439,852
Latest member
balasat

### 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.

### Which adblocker are you using?

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