Solved

EXCEL FUNCTION

Posted on 2014-10-29
3
119 Views
Last Modified: 2014-10-30
I have applied the ate attached function which is simply to apply sumifs to those cells in a column that meet a condition obtained by applying the RIGHT function to the cells in the column.
However all that comes back is a value of 0 .
What have I done incorrectlySUMIFS-WITH-RIGHT.xlsx

  SUMIFS(B3:B9,A3:A9,RIGHT(A3:A9,6)="EXTELP")
0
Comment
Question by:DAVID131
3 Comments
 
LVL 11

Expert Comment

by:zalazar
ID: 40411645
You might need an additional column for this.
If you use an extra column C and in C3 the following formula:
=RIGHT(A3,6)
and then copy this formula down to C9. Then change the formula in B11 to:
SUMIFS(B3:B9,C3:C9,"EXTELP")
0
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 40411774
Use an array formula
=SUM(IF(RIGHT(A3:A9,6)="EXTELP",B3:B9,0))
Insert in cell with Ctrl+Shift+Enter, and Excel enclose the formula in {} brackets.

or use Sumproduct
=SUMPRODUCT(--(RIGHT(A3:A9,6)="EXTELP"),B3:B9)
0
 

Author Closing Comment

by:DAVID131
ID: 40414211
I have opted for this solution as I did not want to add another column
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question