Link to home
Start Free TrialLog in
Avatar of Dan Carp
Dan Carp

asked on

Using Right function in SUMIFS statement

I am trying to utilize a SUMIFS function in the simplified table below.

User generated image
In cell D2, I want to show the sum of all items in column D where column C equals "t" and column B ends with "?".  The formula I am stuck on is:

=SUMIFS(D:D,$C:$C,"t",$B:$B,RIGHT("~?",1))

Open in new window


I know the column C condition works properly, but I am missing what is wrong the the column B condition.  The right function seems to work if I use "RIGHT("AB",2)" but not with any single character.  Could somebody kindly point me in the right direction, please?  Thank you in advance!
Right-Test.xlsx
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Your right function will return ?. What are you expecting?
You can use this
=SUMPRODUCT((RIGHT($B$3:$B$1000,1)="?")*($C$3:$C$1000="t")*D$3:D$1000)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dan Carp
Dan Carp

ASKER

Thanks Rob - exactly what I was looking for and a simpler approach!
Happy to help!