IF & SUMIF Formula problem

[Elite311]

I am trying to create a sheet to track credits that have been given out. Basicaly what I have is 2 sheets 1 the employees update and 1 that gives me totals based on markets and department. I have it working for the most part but the problem I have is when I use the folowing formula:

=IF(Budget!I8=market1,SUMIF(dept,service,amount))

It works perferctly but that only adds the one row with I8 cell and nothing else on the employee sheet. If i change it to:

=IF(market=market1,SUMIF(dept,service,amount))

I get a #VALUE error. I want to get info from the entire column not just one cell. Can anyone help me with this?

I attached a copy of my file aswell. I'm also using excel 2003

Thanks in advance

[ConneXionLost]

Hi Elite311,

Welcome to the forum!

You'll need to correct your named range "amount" to:

=Budget!$H$8:$H$4000

then use this formula in cell Totals!C6:

=SUMPRODUCT(--(market=$A6),--(dept=C$4),amount)

Autofill it to C6:D19

Cheers,

[Elite311]

Thafor the help man, but i'm still having trouble I did what you said and it works great but when i auto fill all the other cells turn to the #VALUE error

I correct the named range but it's different on every cell i click on on the totals sheet not sure why? and when I change it to 4000 on that cell it works and the others goto an error

Am I missing something?

[ConneXionLost]

Did you also get the "$" sign?

[ConneXionLost]

Here's the copy I was playing with:

[Elite311]

Ya it looks like you said, I see what you are doing with that formula just not understanding why the 4000 is making a difference? I'm totally missing something here I just dont see it.

All the values are #VALUE

Heres what my xls looks like now after I updated it

Thanks for the help I really appreciate it

[ConneXionLost]

Yup. In the defined name "amount" you have the formula:

=Budget!$H$8:$H4000

but you're missing the "$" sign in front of the 4000.

Try it like this:

=Budget!$H$8:$H$4000

Cheers,