Sumifs

[Forestq]

hi,

I have 4 selected criteria (data validation list):
column A1 = Region
column C1 = Country
column E1 = Owner
column G1 = Service.

In my data rows region is in column AN, country in column AJ, owner in column AM and service in column AR.

[TABLE="class: outer_border, width: 800"]
<tbody>[TR]
[TD]AN - Region[/TD]
[TD]AJ - Country[/TD]
[TD]AM - Owner[/TD]
[TD]AR -Service[/TD]
[TD]AA - COST[/TD]
[TD]AB - COST TYPE[/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD]NL[/TD]
[TD]Nicola[/TD]
[TD]AFT[/TD]
[TD]200[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD]ESP[/TD]
[TD]Tom[/TD]
[TD]AT[/TD]
[TD]150[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]AF[/TD]
[TD]CON[/TD]
[TD]Simom[/TD]
[TD]AFT[/TD]
[TD]180[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]JP[/TD]
[TD]Chi[/TD]
[TD]AFTX[/TD]
[TD]230[/TD]
[TD]F[/TD]
[/TR]
</tbody>[/TABLE]
example:
user can select only region = EU, region = EU and country = ESP, country = NL and Owner = Nicola etc....for me it`s 14 diffrent cases.

Always I want to get sum (COST, column AA) where COST TYPE= F.

How can I do that?

 

[Marcelo Branco]

Try

=SUMIFS(E39:W39, E36:W36, "F", E37:W37,">=125",E37:W37,"<150")

M.

 

[golf spreadsheet]

Try

=SUMIFS(E39:W39, E36:W36, "F", E37:W37,">=125",E37:W37,"<150")

M.

It worked! Thanks so much for your help!

 

[ezyrecon]

I have this problem with using OR function in Excel.

Here is the data structure

all data starts from row 7

Column A - is date

Column C - is batch ( contains batches like, payroll, payroll check, etc.)

Column H- is the amount I want to add

the criteria date is on A4,

here is formula in error
Sheets("Out").Range("D4").value = _
"=SUMIFS(H7:H125,A7:A125,A4,C7:C125,""*Payroll*"" OR C7:C125,""*Paycheck*"")"

the criteria is on A4. So basically, I want to add all data on H, with a date criteria = to A4, containing a batch code of "Payroll" OR "Paycheck" the batch code is on column C.

Thanks.

 

[Marcelo Branco]

Hi and welcome to Mr Excel forum

Try this

Sheets("Out").Range("D4").Value = _
"=SUM(SUMIFS(H7:H125,A7:A125,A4,C7:C125,{""*Payroll*"",""*Paycheck*""}))"

Hope this helps

M.

 

[ezyrecon]

Hi and welcome to Mr Excel forum

Try this

Sheets("Out").Range("D4").Value = _
"=SUM(SUMIFS(H7:H125,A7:A125,A4,C7:C125,{""*Payroll*"",""*Paycheck*""}))"

Hope this helps

M.

Hey M,

It works I did not know that I need to put the sum. Thanks for the quick reply too. I have been trying to crack my brain for a day now for this. Once again. Thanks.

 

[Marcelo Branco]

Hey M,

It works I did not know that I need to put the sum. Thanks for the quick reply too. I have been trying to crack my brain for a day now for this. Once again. Thanks.

You are welcome. Glad for helping.

M.