SumIfs

[mikecox]

Where can I find a clear explanation of sumifs and sumif ?

Someone gave me a sumifs formula that had a "named range" called lu-Accounts that referred to the range in a worksheet. But I can't find the lu_range that was created and have no idea how it works.

I've attached the spreadsheet as a ref. In it D32 and D21 aren't getting populated and I don't understand why.

Also, at one point I noticed that I can't modify the Description on the Details page because each cell had it's own ref in it. D5 contained )Details!d5) later I went back and found that those ref were gone and I was able to edit the Description. What was happening the when I saw the self references in each cell?

[martindwilson]

the named range refers to
=Lists!$A$2:$A$34
look in namemanager on the insert tab

sumifs explained here
http://spreadsheets.about.com/od/som...p-Tutorial.htm

[abousetta]

If you google the word sumifs you will find a lot of great resources included YouTube videos explaining how they work. A quick look at the first 8 hits were great resources (first 5 websites, and the next 3 were videos)

[mikecox]

the named range refers to
=Lists!$A$2:$A$34
look in namemanager on the insert tab

What "insert tab" are you referring to; the one on the ribbon? I don't see a "namemanger" choice there.

I have figured out the sumifs formula; it's just how it's used in this situation that is confusing me.
Where is the tbl_details and tbl_lists data? Is in a cell in one of the worksheets?

[vlady]

click formulas menu

on the ribbon "Name Manager" just below "Define Name"

[vlady]

you can name range of cells

ex.

you have data in A1 to A10

you can name these cell as one..

...select the cells(A1 to A10)
got to formulas menu
click define name
enter a name for these cells like above post ( tbl_details ) as the name for those cell.

then you can call the range as tbl_details rather than putting it as ranges A1 to A10
and you can find out by yourself the benefits of using named ranges later on.

[mikecox]

click formulas menu

on the ribbon "Name Manager" just below "Define Name"

Ah!!!! Mystery solved! Now I feel like I will begin to with this setup.
Thanks

[mikecox]

you can name range of cells

ex.

you have data in A1 to A10

you can name these cell as one..

...select the cells(A1 to A10)
got to formulas menu
click define name
enter a name for these cells like above post ( tbl_details ) as the name for those cell.

then you can call the range as tbl_details rather than putting it as ranges A1 to A10
and you can find out by yourself the benefits of using named ranges later on.

This mist is clearing!

[martindwilson]

i dont know for the life of me i said insert tab!(mind you i think it was coz of excel 97 insert /names/define), yep formula tab it is