SUMIF function

[merson82]

Hi all,

I am unable to use Sumif function to sum up my Revenue due to the criteria is not matched. 

Eg: I have an salesperson his UID is 500008, his UID in year is 500008_SG. 

So when i try to SUM up his revenue using SUMIF function, using 500008, his revenue for year 2020 is omitted. Could please advise how to solve this? 

Besides, I also need to SUM up revenue for Year 2021 only by salesperson, is there any syntax to use? Currently, i am using pivot table to do this. But, I prefer to have only single formula to solve this. Any 

[#2 · Accepted solution]

@merson82 Use this one in stead:

=SUMPRODUCT(D4:D23*(ISNUMBER(FIND(F5,A4:A23))))

or transform the ID's to Text and use a wildcard in the SUMIF formula.

=SUMIF(A4:A23,F5&"*",D4:D23)

File attached with both options.

[#3]

Hi Riny,
Thank you so much. This is really helpful :) If I would like to sum up only total revenue by Year 2021 by salesperson can I use SUMIFS/ SUMIF function? Any suggestion that I can do this?