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.

AN - Region	AJ - Country	AM - Owner	AR -Service	AA - COST	AB - COST TYPE
EU	NL	Nicola	AFT	200	F
EU	ESP	Tom	AT	150	F
AF	CON	Simom	AFT	180	P
AZ	JP	Chi	AFTX	230	F

<tbody>
</tbody>

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.