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]

Maybe something like this

=SUMIFS(AA:AA,AB:AB,"F",AN:AN,IF(A1="","*",A1),AJ:AJ,IF(C1="","*",C1),AM:AM,IF(E1="","*",E1),AR:AR,IF(G1="","*",G1))

Assumes the user leaves A1, C1, E1 or G1 blank when no criteria for the respective field.

M.

 

[Forestq]

Hi Marcel!,

many, many thanks! Works perfect! That it`s what I was looking for.

Also, I found SIGN method

=SUMPRODUCT(SIGN((AN:AN=A1)+(AJ:AJ=C1)+(AM:AM=E1)+(AR:AR=G1))*AA:AA)

but its not working.


Once again: Thank you!

 

[Forestq]

Hi Marcel!,

We have just one criteria ("F"):
=SUMIFS(AA:AA,AB:AB,"F",AN:AN,IF(A1="","*",A1),AJ:AJ,IF(C1="","*",C1),AM:AM,IF(E1="","*",E1),AR:AR,IF(G1="","*",G1))

How can I add other criteria? example: "F" and "FA" ?

 

[Marcelo Branco]

Maybe this...

=SUMIFS(AA:AA,AB:AB,{"F";"FA"},AN:AN,IF(A1="","*",A1),AJ:AJ,IF(C1="","*",C1),AM:AM,IF(E1="","*",E1),AR:AR,IF(G1="","*",G1))

M.

 

[Forestq]

unfortunately it didn't work

I did as below:

=SUMIFS(AA:AA,AB:AB,"F",AN:AN,IF(A1="","*",A1),AJ:AJ,IF(C1="","*",C1),AM:AM,IF(E1="","*",E1),AR:AR,IF(G1="","*",G1))+SUMIFS(AA:AA,AB:AB,"FA",AN:AN,IF(A1="","*",A1),AJ:AJ,IF(C1="","*",C1),AM:AM,IF(E1="","*",E1),AR:AR,IF(G1="","*",G1))

 

[Marcelo Branco]

unfortunately it didn't work

I did as below:

=SUMIFS(AA:AA,AB:AB,"F",AN:AN,IF(A1="","*",A1),AJ:AJ,IF(C1="","*",C1),AM:AM,IF(E1="","*",E1),AR:AR,IF(G1="","*",G1))+SUMIFS(AA:AA,AB:AB,"FA",AN:AN,IF(A1="","*",A1),AJ:AJ,IF(C1="","*",C1),AM:AM,IF(E1="","*",E1),AR:AR,IF(G1="","*",G1))

Sorry, my bad.

Try this

=SUMPRODUCT(SUMIFS(AA:AA,AB:AB,{"F";"FA"},AN:AN,IF(A1="","*",A1),AJ:AJ,IF(C1="","*",C1),AM:AM,IF(E1="","*",E1),AR:AR,IF(G1="","*",G1)))

M.

 

[Forestq]

Hi, Marcel

now it`s working perfect, many thanks for help with the case!

Regards

 

[Marcelo Branco]

Hi, Marcel

now it`s working perfect, many thanks for help with the case!

Regards

Fores

You are welcome and thanks for the feedback

M.

 

[golf spreadsheet]

I've been working on a golf spreadsheet that calculates my statistics but ran into a problem. I'm trying to solve is the sum of cells in row 39 that meet the following criteria:
1) row 36 contains the letter F
2) row 37 is less than 125

right now I have it correct for only when solving for less than 125
=SUMIFS(E39:W39, E36:W36, "F", E37:W37, "<125")

how would I solve for greater than or equal to 125 and less than 150?


I tried =SUMIFS(E39:W39, E36:W36, "F", E37:W37, ">=125<150") but I’m guessing that’s incorrect since it comes up with a value of 0.


Example data:

row 36: F F MR
row 37: 101 135 145
row 39: 45 65 17