Search : in
By :

Sum a range only if certain text in another

Last answer on Aug 28, 2009 6:09:40 am BST Destro, on Aug 27, 2009 1:31:11 pm BST 
 Report this message to moderators

Hello,
I need a formula to sum the values in a range of cells but only for the cells that have a specific text in a completely different range of cells.

For example,

sum the values for H8:I12
but only if the text in C8:C12 EQUALS "CRM"

I have 5 different text values in order of importance:
LFIP
FIP
ERM
CRM
CIP
Any help would be of great appreciation

Configuration: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Error Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=UTF-8">
<STYLE id=L_10060_1>A {
	FONT-WEIGHT: bold; FONT-SIZE: 10pt; COLOR: #005a80; FONT-FAMILY: tahoma
}
A:hover {
	FONT-WEIGHT: bold; FONT-SIZE: 10pt; COLOR: #0d3372; FONT-FAMILY: tahoma
}
TD {
	FONT-SIZE: 8pt; FONT-FAMILY: tahoma
}
TD.titleBorder {
	BORDER-RIGHT: #955319 1px solid; BORDER-TOP: #955319 1px solid; PADDING-LEFT: 8px; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: middle; BORDER-LEFT: #955319 0px solid; COLOR: #955319; BORDER-BOTTOM: #955319 1px solid; FONT-FAMILY: tahoma; HEIGHT: 35px; BACKGROUND-COLOR: #d2b87a; TEXT-ALIGN: left
}
TD.titleBorder_x {
	BORDER-RIGHT: #955319 0px solid; BORDER-TOP: #955319 1px solid; PADDING-LEFT: 8px; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: middle; BORDER-LEFT: #955319 1px solid; COLOR: #978c79; BORDER-BOTTOM: #955319 1px solid; FONT-FAMILY: tahoma; HEIGHT: 35px; BACKGROUND-COLOR: #d2b87a; TEXT-ALIGN: left
}
.TitleDescription {
	FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: black; FONT-FAMILY: tahoma
}
SPAN.explain {
	FONT-WEIGHT: normal; FONT-SIZE: 10pt; COLOR: #934225
}
SPAN.TryThings {
	FONT-WEIGHT: normal; FONT-SIZE: 10pt; COLOR: #934225
}
.TryList {
	MARGIN-TOP: 5px; FONT-WEIGHT: normal; FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: tahoma
}
.X {
	BORDER-RIGHT: #955319 1px solid; BORDER-TOP: #955319 1px solid; FONT-WEIGHT: normal; FONT-SIZE: 12pt; BORDER-LEFT: #955319 1px solid; COLOR: #7b3807; BORDER-BOTTOM: #955319 1px solid; FONT-FAMILY: verdana; BACKGROUND-COLOR: #d1c2b4
}
.adminList {
	MARGIN-TOP: 2px
}
</STYLE>
<META content="MSHTML 6.00.2800.1170" name=GENERATOR></HEAD>
<BODY bgColor=#f3f3ed>
<TABLE cellSpacing=0 cellPadding=0 width="100%">
  <TBODY>
  <TR>
    <TD class=titleborder_x width=30>
      <TABLE height=25 cellSpacing=2 cellPadding=0 width=25 bgColor=black>
        <TBODY>
        <TR>
          <TD class=x vAlign=center align=middle>X</TD>
        </TR>
        </TBODY>
      </TABLE>
    </TD>
    <TD class=titleBorder id=L_10060_2>Network Access Message:<SPAN class=TitleDescription> The page cannot be displayed</SPAN> </TD>
  </TR>
  </TBODY>
</TABLE>

<TABLE id=spacer>
  <TBODY>
  <TR>
    <TD height=10></TD></TR></TBODY></TABLE>
<TABLE width=400>
  <TBODY>
  <TR>
    <TD noWrap width=25></TD>
    <TD width=400><SPAN class=explain><ID id=L_10060_3><B>Explanation:</B></ID></SPAN><ID id=L_10060_4> The request timed out before the page could be retrieved. </ID><BR><BR>
    <B><SPAN class=tryThings><ID id=L_10060_5><B>Try the following:</B></ID></SPAN></B> 
      <UL class=TryList>
        <LI id=L_10060_6><B>Refresh page:</B> Search for the page again by clicking the Refresh button. The timeout may have occurred due to Internet congestion.
<LI id=L_10060_7><B>Check spelling:</B> Check that you typed the Web page address correctly. The address may have been mistyped.
<LI id=L_10060_8><B>Contact website:</B> You may want to contact the website administrator to make sure the Web page still exists. You can do this by using the e-mail address or phone number listed on the website home page.

      </UL>
<ID id=L_10060_9>If you are still not able to view the requested page, try contacting your administrator or Helpdesk.</ID> <BR><BR>
    </TD>
  </TR>
  </TBODY>
</TABLE>

<TABLE id=spacer><TBODY><TR><TD height=15></TD></TR></TBODY></TABLE>

<TABLE width=400>
  <TBODY>
  <TR>
    <TD noWrap width=25></TD>
    <TD width=400 id=L_10060_10><B>Technical Information (for support personnel)</B> 
      <UL class=adminList>
        <LI id=L_10060_11>Error Code 10060: Connection timeout
<LI id=L_10060_12>Background: The gateway could not receive a timely response from the website you are trying to access. This might indicate that the network is congested, or that the website is experiencing technical difficulties.
<LI id=L_10060_13>Date: 8/27/2009 1:27:08 PM [GMT]
<LI id=L_10060_14>Server: bp1pbupx001.bp1.ad.bp.com
<LI id=L_10060_15>Source: Firewall

      </UL>
    </TD>
  </TR>
  </TBODY>
</TABLE>

</BODY>
</HTML>

Best answers for « sum a range only if certain text in another » in :
[Excel 2003] - IF Function using Dates &amp; Text Show[Excel 2003] - IF Function using Dates & Text Issue Solution Note Issue I'm trying to write a function for a cell which refers to a date in another cell - which if it is between 01/01/1994 and 31/12/1995 the cell will read U16, but...
[VBA] Deleting a word in a range of cell Show[VBA] Deleting a word in a range of cell In the case you want to delete a word in a sentence, just create a small macro that removes the word. But it will become difficult when you have word like, for example, "Theword" or "THEWORD" or...
[Excel]changing cell formula to text Show[Excel]changing cell formula to text Issue Solution Notes Issue Consider that I have: In cell A4, it contains a formula =Sum(A1:B3)+A3/B2. How to extract this formula in cell A6 as a string of text? That is ... I want cell A6...
Download Recover Deleted text Messages Show
Download Text to MP3 ShowText to MP3 allows to convert text files into WAV or MP3 files. It can solve your daily problem about electronic texts. With this software published by United Research Labs, you can listen to your documents as MP3 files in 11 different languages....
Download Grab Text ShowDescription The application is designed by GrabText.com. So far it has won many awards from some places and it is also well known worldwide, even being used in companies. Grab Text is a tool that is based on text recognition. The application allows...
Spreadsheet - Text Operators ShowConcatenation Operator Spreadsheets generally use an oeprator, called a concatenation operator , that allows two text values to be added together. The concatenation operator, written as &, is used as follows: In the above example, the cell...
Ask your question Reply

1

venkat1926, on Aug 28, 2009 2:25:05 am BST

Your information is not complete
I assume the five text strings are in H8 to H12
and in I6 to I12 there are some numbers

now use this formula

=SUM(IF(H8:H12="CRM",I8:I12))
This is ana rray formula so INVOKE THIS FORMULA BY HITTING
CONTROL+SHIFT+ENTER.

   
Reply to venkat1926

2

 Destro77, on Aug 28, 2009 6:09:40 am BST

Thank you very much!

You know I was sure I tried that. Must have made a dumb error in the formula somewhere. It was 4am my time.
I know... excuses, excuses. Knew it was an easy one. Thanks again, much appreciated.

   
Reply to Destro77
Ask your question Reply
They need your help