IF + sumif (vlookup) returning the incorrect values

[maltoses]

Hi all,

I am very new to Excel function, I was asked to create a report to show the customer's revenue based on country and booking type.
I tried using the sumif+vlookup formula, it works with returning the result with all countries and any booking type.
But it seems no luck for me, which I added the IF statement in front of the sumif formula to see if it can help to filter out the criteria.

I read some article said the limitation of Sumif statement, but I have no idea which formula I should use, see if someone can help me to fix this formula for me, thanks.

This is the formula I use
A4 = country
B4= booking type

'=IF(AND(A4="JP",B4<>"Renewal"),SUMIF(E:E,VLOOKUP(H4,D:E,2,FALSE),C:C),"")

 

[Fluff]

Hi & welcome to MrExcel.
Maybe

=SUMIFS(C:C,A:A,"JP",B:B,"<>Renewal",E:E,I3)

 

[etaf]

=SUMIFS()
should work

=SUMIFS(C3:C1000, A3:A1000, H7, E3;E1000, H2,B3:B1000, "<renewal>" )
But you maybe able to do a Indirect or some other way to use the cell for the <>Renewal

 

[maltoses]

Hi & welcome to MrExcel.
Maybe

=SUMIFS(C:C,A:A,"JP",B:B,"<>Renewal",E:E,I3)

Thanks Fluff,
This formula works well, I tried to search the formula on Google to understand it more, coz I do not really understand how it works.

 

[Alex Blakenburg]

Below is a 2.5 minute video from Contextures. There are longer ones but this might be enough for you.
The syntax is:-
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
What this means in your case

=SUMIFS(C:C,	Sum what is in column C (the sum_range)
	but only when
A:A,"JP",	the value in Column A for the same row = JP (Column A is the criteria range1 and JP is the criteria1)
B:B,"<>Renewal",	and the value in Column B for the same row does not equal Renewal
E:E,I3)	and the value in Column E for the same row = what is in I3 (which I believe is the customer)

 

[Fluff]

Glad we could help & thanks for the feedback.