# Problem with array in SUM(SUMIF formula

1. ## Problem with array in SUM(SUMIF formula

Hi, I have some probblems to generate a working formula like sum(sumif....)

I need to read all the resources codes of a dept form sheet department an put a formula in sheet Totals by type via VBA code
This because resources in department may change (add or delete resources)

If i have one resource only in a despt i create a sumif formula and work
If there are more than one resource i need to put a formula like sum(sumifs...) and put array into the criteria

I have created a static version and it work but when i try to automate the formula creation with VBA don't work! Can anyone help me?

P.S. Sorry for my very bad english  Register To Reply

2. ## Re: Problem with array in SUM(SUMIF formula

You are almost there. I think this is the formula you need
=SUMIFS('DATI ESPORTATI'!\$BN:\$BN,'DATI ESPORTATI'!\$A:\$A,'GRUPPI RISORSE'!\$D3,'DATI ESPORTATI'!\$BP:\$BP,B\$1,'DATI ESPORTATI'!\$BO:\$BO,"SER")

Notice the placement of the dollar sign (\$). These keep things from changing when you copy a formula from one cell to another.

For example if you use \$A\$1 in a formula, no matter where that formula is used, the \$A\$1 will not change.

Now in your original formula, you made reference to "ALTRO", "TYPE 1", etc. These are your column headers. So for the first formula we want it to look at cell B1, for the next formula cell C1, and so on. So we want the column to change, but not the row. So we can use B\$1 in the formula.

Likewise, you use GRUPPI RISORSE'!D3 in the formula. We want to keep the column as D always and let the row vary so we can use GRUPPI RISORSE'!\$D3.

Likewise, I locked down the ranges BN:BN, BP:BP and BO:BO so they don't change when the formula is copied from one column to another.

You should be able to copy this formula to the rest of the cells that need them.

Read up on absolute reference and relative reference for Excel.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 