sumifs choose,match fucntion

=SUMIFS(CHOOSE(MATCH(F$17,$I$1:$L$1,0),$I$2:$I$13,$J$2:$J$13,$K$2:$K$13,$L$2:$L$13),$C$2:$C$13,$E18,$E$2:$E$13,$B18,$F$2:$F$13,$A18,$G$2:$G$13,$C18,$H$2:$H$13,$D18)

i want vba code for this excel

gurusiva,

I would "name" the various columns in your Database as ranges, then "define" the names in the Macro,

You can then have a one line code that finds every cell in each range that matches the criteria, and puts the appropriate total where you want.

There is a very clear step-by-step guide on this at http://www.exceltip.com/tips/sumif-f...oft-excel.html where the Macro structure finds the total from three criteria (salesperson, region and product) as follows:

Please Login or Register  to view this content.

Hope this helps

Ochimus

hi Ochimus

very thanks for the quick response

but i want sumifs(choose(match(,,,),,,,,,,,,),,,,,,)function please refer the attachment and help on the same

gurusivakumaran,

I don't understand your comment?

Your original post says categorically "i want vba code for this"

I gave you the VBA code.

It allows you to get the total based on three variables, with a link to a practical example that you can adapt,

What else were you expecting?

Ochimus

hi

please see my attachment

formula applied for yellow column

i want vba for that formula

refer the data for your reference pls help

data is there in data sheet in that attached book11.xlsx

i want summary in that first sheet

urgent anybody help

OK, try the attached.

Data sheet has "Dummy" database covering four weeks.

Summary sheet has "DropLists" in C4 to select the "RGM_HQ1", and in C6 to select the Buyer Number (using the fixed list prevents typing errors).

When you click the Button, the Code below checks each row in the Data worksheet, and copies onto the Summary sheet every row matching the criteria you specify.

Please Login or Register  to view this content.

Ochimus

Dear Ochimus,

excellent very thanks really great very very thanks for helping quick response

Dear ochimus,

one more doubt , if the week is duplicate means it automatically sum how to do any idea ???

please help to me

gurusivakumaran,

Been "afd" for week for family reasons, Will have a look at this tomorrow for you, and see if I can give you the code that will show one number for all the Database rows meeting your different criteria.

Ochimus

Duplicate posting - please ignore

no , this is not duplicate its spelling mistake , ill correct my data

please help

gurusivakumara,

FDibbins is correct - It was my message asking about the two tables that was "duplicated" by mistake.

Ochimus