#### SumIF function #12

```I apologize for not making myself clear.  I appreciated the response.  And
allow me to qualify my original question further.   I know I can do a pivot
table and obtain the results, but I am trying to avoid that.

See my example below -  the question is how do I do a sumif function within
a summary view when I want to recap hundreds of rows by alpha FERC Codes and
sum associated values within the aging bucket column?  I included an excerpt
of 5 records  under 4 column headings, and then I show the summary of those
five records below by FERC Code by aging bucket.  The countif function worked
when it came to counting the FERC codes, but I am stuck on calculating the
summary values associated to the FERC Code by aging bucket with the sumif
function.   This summary section is giving me heartache.    Thanks

Detail- Below is an excerpt.  But we have hundreds of rows...
FERC Code FERC Current	FERC >30 Days	FERC > 60
TG	         10
PG	 	                         20
PG	 	                       100
SG	 	                       300
TG	 	 	                                        25
Total	5 	         10 	                       420	                        25

This is what I am looking for below as the answer.  Can one use the sumif
function to add values by within buckets by a FERC Code?

Count	Current	FERC >30	FERC >60
TG	2	10 	0 	   25
PG	2	0 	120 	    0
SG	1	0 	300 	    0
Summary	5	10 	420 	   25

```
 0
ACDenver (6)
8/17/2005 6:56:11 PM
excel.misc 78881 articles. 5 followers.

2 Replies
470 Views

Similar Articles

[PageSpeed] 37

```COUNTIF and SUMIF will do this will do this

=COUNTIF(A2:A100,"TG") gives the count
=SUMIF(A2:A100,"TG", B2:B100) sums the current
etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"ACDenver" <ACDenver@discussions.microsoft.com> wrote in message
news:B7615FCA-8654-4828-893F-44E90D5D137C@microsoft.com...
> I apologize for not making myself clear.  I appreciated the response.  And
> allow me to qualify my original question further.   I know I can do a
pivot
> table and obtain the results, but I am trying to avoid that.
>
> See my example below -  the question is how do I do a sumif function
within
> a summary view when I want to recap hundreds of rows by alpha FERC Codes
and
> sum associated values within the aging bucket column?  I included an
excerpt
> of 5 records  under 4 column headings, and then I show the summary of
those
> five records below by FERC Code by aging bucket.  The countif function
worked
> when it came to counting the FERC codes, but I am stuck on calculating the
> summary values associated to the FERC Code by aging bucket with the sumif
> function.   This summary section is giving me heartache.    Thanks
>
> Detail- Below is an excerpt.  But we have hundreds of rows...
> FERC Code FERC Current FERC >30 Days FERC > 60
> TG          10
> PG                          20
> PG                        100
> SG                        300
> TG                                         25
> Total 5          10                        420                         25
>
> This is what I am looking for below as the answer.  Can one use the sumif
> function to add values by within buckets by a FERC Code?
>
> Count Current FERC >30 FERC >60
> TG 2 10 0    25
> PG 2 0 120     0
> SG 1 0 300     0
> Summary 5 10 420    25
>

```
 0
bob.phillips1 (6510)
8/17/2005 8:25:14 PM
```Assumptions:

Columns A, B, C, and D contain your source table

First row contains the following labels...

B1:  Current

C1:  30 Days

D1:  60 Days

Columns G through K contain your result table

Column G, starting with G2, contains your list of codes, TG, PG, SG

H1:K1 contains the following labels...

H1:  Count

I1:  Current

J1:  30 Days

K1:  60 Days

Formulas:

H2, copied down:

=COUNTIF(\$A\$2:\$A\$6,G2)

I2, copied down and across:

=SUMIF(\$A\$2:\$A\$6,\$G2,INDEX(\$B\$2:\$D\$6,0,MATCH(I\$1,\$B\$1:\$D\$1,0)))

Hope this helps!

In article <B7615FCA-8654-4828-893F-44E90D5D137C@microsoft.com>,
"ACDenver" <ACDenver@discussions.microsoft.com> wrote:

> I apologize for not making myself clear.  I appreciated the response.  And
> allow me to qualify my original question further.   I know I can do a pivot
> table and obtain the results, but I am trying to avoid that.
>
> See my example below -  the question is how do I do a sumif function within
> a summary view when I want to recap hundreds of rows by alpha FERC Codes and
> sum associated values within the aging bucket column?  I included an excerpt
> of 5 records  under 4 column headings, and then I show the summary of those
> five records below by FERC Code by aging bucket.  The countif function worked
> when it came to counting the FERC codes, but I am stuck on calculating the
> summary values associated to the FERC Code by aging bucket with the sumif
> function.   This summary section is giving me heartache.    Thanks
>
> Detail- Below is an excerpt.  But we have hundreds of rows...
> 	FERC Code FERC Current	FERC >30 Days	FERC > 60
> 	TG	         10
> 	PG	 	                         20
> 	PG	 	                       100
> 	SG	 	                       300
> 	TG	 	 	                                        25
> Total	5 	         10 	                       420	                        25
>
> This is what I am looking for below as the answer.  Can one use the sumif
> function to add values by within buckets by a FERC Code?
>
> 	Count	Current	FERC >30	FERC >60
> TG	2	10 	0 	   25
> PG	2	0 	120 	    0
> SG	1	0 	300 	    0
> Summary	5	10 	420 	   25
```
 0
domenic22 (716)
8/17/2005 8:47:27 PM

Similar Artilces:

Merge records function
Is it possible to reuse the record merge function in CRM. Fx to call the function with a valid Account GUID with a URL-call to mergerecords ? I want this to open the merge windows with the GUID as the master record. ...

Sumifs with data and dates
I need a formula for each item in col A that was added during 2010 only. the spreadsheet will have years of data. A B Expense Date Amount Furniture 1/12/2010 \$326.00 Inventory 1/15/2010 \$250.00 Utilities 1/15/2010 \$98.00 Rent 1/29/2010 \$1,000.00 Rent 1/30/2010 \$536.00 how can I Sumif with Critera in Col A and only for a specific year? Gary Submitted via EggHeadCafe - Software Developer Portal of Choice Silverlight, WPF, XAML and InnerWorkings Coding Challenge http://www.eggheadcafe.com/tutorials/aspnet/f...

Excel functions
Hi everyone, Hope somebody could help me with these problems. Problem #1. row 1 =DSUM(database,field,'\$A4:\$A5) where A4 = "destin"; A5 = "Consolidator 1" row 2 =DSUM(database,field,'\$A6:\$A7) where A6 = "destin"; A7 = "Consolidator 2" ..... row n =DSUM(database,field,'\$An:\$Am) where An = "destin"; Am = "Consolidator n" I waste a lot of rows for this criteria only because I do not know ho to create it another way, I mean (A4, A6 and so on) Problem #2 I have named several range of cells in workbook. I want to change...

Writting Function using VBA
Hi I am trying to write a function to return an address but instead I get #VALUE!. Public Function fnd(a, b) fnd = Range(a).Find(b).Address End Function Please help. Thanks. ..Find won't work in UDF's called from the worksheet until xl2002. Depending on the range (a), you could use application.match() through each column. If the range is small, you could just loop through the values in that range, too. nc wrote: > > Hi > > I am trying to write a function to return an address but > instead I get #VALUE!. > > Public Function fnd(a, b) > >...

HELP! 12-06-07
Excel won't open and I've both tried re-installing it, and rebooting what should I do? On Thu, 6 Dec 2007 00:42:20 -0500, avarage wrote: > Excel won't open and I've both tried re-installing it, and rebooting what > should I do? You have posted this message to the wrong newsgroup. The access in this groups name refers to Microsoft Access, a database program. Please repost to the correct newsgroup for whatever Office program you are using. I would suggest you include your Windows and Office version number in the message. -- Fred Please respond only to this newsgrou...

converting hand drawn sketches into Visio Cross Functional Horizon
Hi , I am new to Visio 2003 . Can anybody tell me how can one convert hand drawn sketches into Visio Cross Functional Horizontal diagrams? Thanks Harsha Are you asking about a way to scan images into Visio and transform them into drawings? Or are you asking how to assemble a Cross Functional diagram? To make a Cross Functional diagram, you first choose how many bands to create. Then you click on the headings for the bands to change the text to the names you want. Switch to the Basic Flowchart shapes in the Shapes Window and drag out the Process shapes to create the steps in you...

Excel super slow on paste function in 2007
When you copy and paste one cell to another I get the circular waiting bar and after 4-5 seconds it pastes. If I'm doing a bunch of cells, it still takes 4-5 seconds per cell and the cells will appear right to left, 1 every 4-5 seconds until it's complete. If I hit ESC, everything copies and pastes right away. This started happening when we converted a 2003 file to 2007. It affects everyone so it's not my PC, it's the document. There are no active add-ons but I recently downloaded an add-in that I got from this discussion board to find hidden links (it's d...

Automatic Functions in Excel
Hi There I am developing a Time Sheet in Excel for Staff to record times worked on it. I am using the 1904 date system as occasionally a negative total will be displayed. I would like it if someone was on annual leave they would be able to record this by typing in "Annual Leave" or "A/L" or something similar then the total for that day to display as 7:00 (this is in hours). At present the "total" column runs a calculation of the time finished minus the time started. Is this possible? Regards Colin Hi Colin, StartTime in column A, end ti...

is there a baby-sitting function in Exchange
SBS2003 Premium. One user is not very diligent on reading and responding to emails. He is on the road quite a bit and when he gets back he will not remember that one important email that we needed to respond to. Is there a way that I can have other users have his mailbox open as well, so that they can monitor it and make sure things get replied to? There appears to be such functionality in OL2003, but it just doesn't open the mailbox. Plus his password changes every 30 days, as per the administrator settings, how do we deal with that? Thanks in advance, Robert Assign Full Mailbox ...

query criteria 12-29-07
I have a criteria test in the [DR].[Received Date] field of a query. The criteria is based on data which is entered into a form. >=(IIf(IsNull([Forms]![CSR dg]![txtEarliestReceivedDate]),[DR].[Received Date],[Forms]![CSR dg]![txtEarliestReceivedDate])) The true side of the IIF allows for all received dates if no data is entered into the form. I would like to instead not do a criteria test at all if there is no date entered into the form. I've tried things like "" or NULL in the criteria, but to no avail. The reason for wanting to do this, is that I have two tables linke...

How to Declare Pointer to Member Function?
I have a thread that needs to make data visible to a function inside a separate class. In the past, I was using PostMessage to get my information to the class function, but occasionally a message gets lost this way. I could declare the class function as static, but then the function would not be able to interact with other parts of the class. The data is thread safe. // header: typedef void (*PtrToLpMsg)(LPTSTR lpMsg); PtrToLpMsg g_AddStatusMsgFn; // code: CMain::CMain() { g_AddStatusMsgFn = AddStatusMsgA; } void CMain::AddStatusMsgA(LPTSTR lpMsg) { // other code } On Mon, 16 ...

I can't install the 12.2.1 update on Snow Leopard
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Office 2008 12.2.1 Update can't be installed on this disk. A version of the software required to install this update was not found on this volume. I guarantee a legitimate copy of Office 2008 for Mac is on my iMac. Any suggestions? On 9/30/09 3:43 PM, in article 59b7d9fc.-1@webcrossing.caR9absDaxw, "kpainte1@officeformac.com" <kpainte1@officeformac.com> wrote: > Office 2008 12.2.1 Update can't be installed on this disk. A version of the > software required to install this update was n...

Function to control how data displayed
I have 2 columns that I exported from Access to Excel. In Access th columns were Yes or No. In excel they display as True or False. I wan them to show as Yes or No what is the function to make this happen? Thank you! -- LOgle531 ----------------------------------------------------------------------- LOgle5318's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2659 View this thread: http://www.excelforum.com/showthread.php?threadid=39860 LOgle5318, Assuming your True and False are in columns A & B then In C1 put =IF(A1="True","Yes") and...

XL2003: INDIRECT() function changes calling cell
Hello all, It appears that the INDIRECT() function changes the calling cell. Let me try to explain: I have a dynamic named range "NR6.PerMonth": - When "=NR6.PerMonth" is in cell C27 on the "Summary" sheet, it will refer to/return cell C982 on the "6" sheet. - When "=NR6.PerMonth" is in cell D27, it will refer to/return cell D982....and so on and so forth, going across. BUT!!! (A26 on the "Summary" sheet is a formula that returns the number 6...A982 on the "6" sheet is a label): - When '=I...

Upgrade from 12.0.0 to 12.1.0 not possible
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel After downloading the huge update file and doing the first clicks here and there in the update software it starts to look for the hard disk where the office is installed. It shows the HD but it is dimmed and not possible for me to select. <br><br>Any suggestions? <br><br>Werner Yes I did booted from that driva and yes I am admin. <br><br>The message I get from the installer is : Office 2008 SP1 Uptade (12.1.0) can not be installed on this disk. A version of the software requ...

Average Function #2
Hi Everyone, I am using Excel 2003. I understand there is probably a very sophisticated way to get what I want but I need to learn the simple way first. I have a data sheet named "MICU". I inserted another sheet in the workbook called "Stats". I want to break down the data by month on my new Stats sheet. The MICU sheet has many columns of info. The 2 I think I need to use is a column for start date "ThpyStDtTm" (Date and Time field 3/14/09 18:41) and a column called "VentLOSDays" (Number format with 2 decimal places). Column A Column...

How to call a function from another workbook
Dear sir, how can i call a function or procedure that contained in another workbook ? thanks Joe Hi When you want to use an UDF or procedure in several workbooks, then save it into a module of Personal Macro Workbook (Personal.xls, it's created automatically, when you select Personal Macro Workbook as destination to save a newly-created macro, and later is loaded automatically whenever you start Excel). -- Arvi Laanemets (When sending e-mail, use address arvil<At>tarkon.ee) "Joe" <Joe@discussions.microsoft.com> wrote in message news:AF3EABAE-6751-4917-88F1-1A5...

Function Call Problem
Could anyone help me with this Dialog App (first visual app). I am writing a test program for using Tab Controls. However, I have found that I am not sure how to call a class member function from the main dialog class to another dialog class. The program compiles normally. But the function call is causing an "Assertion Failed" error message whenever the button is clicked. It is used to print a number in a dialog of a tab control, and it also prints the same number in the main dialog. Both of these numbers are printed in Static Text Controls. This is the code from the main Dial...

count function in pivot table
How to count the number of types under certain group in a pivot table: Such as one sku occurs twice under one customer, I only need 1 in pivot table, instead of count the numbers of occurrences: 2 A pivot table won't calculate a unique count. You could add a column to the list, then add that field to the pivot table. There's an example here: http://www.contextures.com/xlPivot07.html#Unique Holly wrote: > How to count the number of types under certain group in a pivot table: > > Such as one sku occurs twice under one customer, I only need 1 in pivot > table, inst...

Money Won't Function
Greetings, I'm using Money 2006 Standard Edition, and I wanted to try the latest version of Money Essentials. The FAQ said there were no problems installing the trial alongside your existing installation because it installs into a separate directory. Well, my trial just expired, and now that I removed Money Essentials, I now have two problems: 1. Money 2006 no longer plays sounds. 2. When I click the "download" button at my bank's web site, the information no longer gets downloaded into Money. Instead I get a file download box with a filename ending in either *.ofx o...

Sub not Function!
To make a comparison of two tests I want to put the values next to eac other on a sheet. To do that, I have two Combo Boxes with each the same 15 choises...yo have to choose two different tests. Now, I'm having a problem to make my code short: -Dim leftChoise As Integer leftChoise = Sheets("Keuze").Range("E1").Value Dim rightChoise As Integer rightChoise = Sheets("Keuze").Range("K1").Value Select Case leftChoise Case 1 PutLeftOnReport ("B7") Case 2 PutLeftOnReport ("B29") Case 3 PutLeftOnReport ("B51") Don't u...

PivotTable canned functions
I am using Excel 2000 (9.0.2720). In my work I often faced with the task of creating ratios of CurrentListValue / CurrentSaleValue then need to perform various calculations on those ratios to identify and conclude on Central Tendencies (among other things) within categories from a parent array. The list of functions available for PivotTable calculation (I am assuming under the hood these are Array Functions) is very narrow. Only providing for Average, Standard Deviation, Min, Max, etc. Is there a way to add to this list, such as but not limited to, Average Deviation, Median, Geometr...

Compile errors in function when converting from 97 to 2000
I am trying to convert an Access 97 mdb file to 2000...I got a compile error and when I run the debugger it stops at this function... It highlights the last section...RankCheck = result ....error states...Function call on lefthand side of assignment must return Variant or Object. Any ideas on what it should be changed to? Public Function RankCheckOrig(current_value As Integer) As Integer Dim previous_value As Integer Dim previous_rank As Integer Dim result As Integer previous_value = Forms![Rankvariables]![previous_value] previous_rank = Forms![Rankvariables]![previous_rank] If current_v...

Table Command function
Can you please explain the table command function under "data" in excel, with a simple example because I don't know how one can use this function in Microsoft Excel. Thank you. Bill Here are a couple of examples http://www.dicks-blog.com/excel/2004/09/data_table_basi.html http://www.dicks-blog.com/excel/2004/10/retail_pricing.html -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Bill T." <Bill T.@discussions.microsoft.com> wrote in message news:6F1E220D-3FED-451B-85B6-8514634C3F6A@microsoft.com... > Can you please explai...

Access 2003
We have a timekeeping database that uses the Date Picker function to choose the week ending date when our employees enter their time. Our payroll week ends on a Sunday. Is it possible to make the date picker allow only Sunday dates as their choice? If so, how do I set this up? Thanks for your help! Without the Date Picker function to review it is impossible for anyone to tell. Which date picker are you using? What you can do howeverm is use a control event to validate the value entered by the user using the afterupdate event and code like If WeekdayName(DatePart("w",[txt...