sumifs help
I have the following formula. =SUMIFS(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) It now needs to be changed to a formula that can handle text instead of numbers. How do i do it?
|
0 |
|
Utf
1/20/2010 6:48:01 AM
excel.programming
6508 articles. 
2 followers.
11 Replies
3090 Views
Use Countif instead of Sumif from =SUMIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) to =CountIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) Do you really have a function Countifs with an "S" at the end? thie maybe an UDF that needs to be modified. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=171628 [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
|
|
0 |
|
joel
1/20/2010 8:05:57 AM
SUMIFS/COUNTIFS are Excel 2007 functions! Bob "joel" <joel.452wlb@thecodecage.com> wrote in message news:joel.452wlb@thecodecage.com... > > Use Countif instead of Sumif > > from > > =SUMIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) > > to > > =CountIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) > > Do you really have a function Countifs with an "S" at the end? thie > maybe an UDF that needs to be modified. > > > -- > joel > ------------------------------------------------------------------------ > joel's Profile: 229 > View this thread: > http://www.thecodecage.com/forumz/showthread.php?t=171628 > > [url="http://www.thecodecage.com"]Microsoft Office Help[/url] >
|
|
0 |
|
Bob
1/20/2010 9:03:54 AM
Tried countif comes up with "too few arguments" I looked at the syntax of countifs and there doesnt appear to be an equivalent "sumrange", which in the formula is Table1[2]. Some additional info that you may require, Table1[2] contains either a X or is blank and there will only ever be 1 cell in the range Table1[2] that is a match. Any more ideas? Regards Primed Hi "Bob Phillips" wrote: > SUMIFS/COUNTIFS are Excel 2007 functions! > > Bob > > "joel" <joel.452wlb@thecodecage.com> wrote in message > news:joel.452wlb@thecodecage.com... > > > > Use Countif instead of Sumif > > > > from > > > > =SUMIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) > > > > to > > > > =CountIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) > > > > Do you really have a function Countifs with an "S" at the end? thie > > maybe an UDF that needs to be modified. > > > > > > -- > > joel > > ------------------------------------------------------------------------ > > joel's Profile: 229 > > View this thread: > > http://www.thecodecage.com/forumz/showthread.php?t=171628 > > > > [url="http://www.thecodecage.com"]Microsoft Office Help[/url] > > > > > . >
|
|
0 |
|
Utf
1/21/2010 2:28:01 AM
COUNTIFS doesn't require a sumrange. HTH Bob "primed" <primed@discussions.microsoft.com> wrote in message news:495E61B5-6539-4F23-B64D-5C7A656228CC@microsoft.com... > Tried countif comes up with "too few arguments" > I looked at the syntax of countifs and there doesnt appear to be an > equivalent "sumrange", which in the formula is Table1[2]. > > Some additional info that you may require, > Table1[2] contains either a X or is blank and there will only ever be 1 > cell > in the range Table1[2] that is a match. > > Any more ideas? > > Regards > Primed > > > > Hi > > "Bob Phillips" wrote: > >> SUMIFS/COUNTIFS are Excel 2007 functions! >> >> Bob >> >> "joel" <joel.452wlb@thecodecage.com> wrote in message >> news:joel.452wlb@thecodecage.com... >> > >> > Use Countif instead of Sumif >> > >> > from >> > >> > =SUMIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) >> > >> > to >> > >> > =CountIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) >> > >> > Do you really have a function Countifs with an "S" at the end? thie >> > maybe an UDF that needs to be modified. >> > >> > >> > -- >> > joel >> > ------------------------------------------------------------------------ >> > joel's Profile: 229 >> > View this thread: >> > http://www.thecodecage.com/forumz/showthread.php?t=171628 >> > >> > [url="http://www.thecodecage.com"]Microsoft Office Help[/url] >> > >> >> >> . >>
|
|
0 |
|
Bob
1/21/2010 8:54:14 AM
Correct, which makes the countif solution not workable. ie i have to search two colums for two criteria matches then copy the result from the matching row in column (Table1[2]). Table1[2] used to contain a number 1 it now contains an X or is blank. Any more suggestions? Your help much appreciated. Regards Primed "Bob Phillips" wrote: > COUNTIFS doesn't require a sumrange. > > HTH > > Bob > > "primed" <primed@discussions.microsoft.com> wrote in message > news:495E61B5-6539-4F23-B64D-5C7A656228CC@microsoft.com... > > Tried countif comes up with "too few arguments" > > I looked at the syntax of countifs and there doesnt appear to be an > > equivalent "sumrange", which in the formula is Table1[2]. > > > > Some additional info that you may require, > > Table1[2] contains either a X or is blank and there will only ever be 1 > > cell > > in the range Table1[2] that is a match. > > > > Any more ideas? > > > > Regards > > Primed > > > > > > > > Hi > > > > "Bob Phillips" wrote: > > > >> SUMIFS/COUNTIFS are Excel 2007 functions! > >> > >> Bob > >> > >> "joel" <joel.452wlb@thecodecage.com> wrote in message > >> news:joel.452wlb@thecodecage.com... > >> > > >> > Use Countif instead of Sumif > >> > > >> > from > >> > > >> > =SUMIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) > >> > > >> > to > >> > > >> > =CountIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) > >> > > >> > Do you really have a function Countifs with an "S" at the end? thie > >> > maybe an UDF that needs to be modified. > >> > > >> > > >> > -- > >> > joel > >> > ------------------------------------------------------------------------ > >> > joel's Profile: 229 > >> > View this thread: > >> > http://www.thecodecage.com/forumz/showthread.php?t=171628 > >> > > >> > [url="http://www.thecodecage.com"]Microsoft Office Help[/url] > >> > > >> > >> > >> . > >> > > > . >
|
|
0 |
|
Utf
1/22/2010 3:26:01 AM
HI I am confused now! You say that you want to 'copy' the result from the matching row !?! I have a feeling that a SUMPRODUCT formula can do what you need, but to be sure, can you post some sample data, with an description of what you want and the expected result. Regards, Per On 22 Jan., 04:26, primed <pri...@discussions.microsoft.com> wrote: > Correct, which makes the countif solution not workable. ie i have to sear= ch > two colums for two criteria matches then copy the result from the matchin= g > row in column (Table1[2]). =A0 > > Table1[2] used to contain a number 1 it now contains an X or is blank. > > Any more suggestions? > Your help much appreciated. > > Regards > Primed > > > > "Bob Phillips" wrote: > > COUNTIFS doesn't require a sumrange. > > > HTH > > > Bob > > > "primed" <pri...@discussions.microsoft.com> wrote in message > >news:495E61B5-6539-4F23-B64D-5C7A656228CC@microsoft.com... > > > Tried countif comes up with "too few arguments" > > > I looked at the syntax of countifs and there doesnt appear to be an > > > equivalent "sumrange", which in the formula is Table1[2]. > > > > Some additional info that you may require, > > > Table1[2] contains either a X or is blank and there will only ever be= 1 > > > cell > > > in the range Table1[2] that is a match. > > > > Any more ideas? > > > > Regards > > > Primed > > > > Hi > > > > "Bob Phillips" wrote: > > > >> SUMIFS/COUNTIFS are Excel 2007 functions! > > > >> Bob > > > >> "joel" <joel.452...@thecodecage.com> wrote in message > > >>news:joel.452wlb@thecodecage.com... > > > >> > Use Countif instead of Sumif > > > >> > from > > > >> > =3DSUMIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) > > > >> > to > > > >> > =3DCountIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) > > > >> > Do you really have a function Countifs with an "S" at the end? =A0= thie > > >> > maybe an UDF that needs to be modified. > > > >> > -- > > >> > joel > > >> > ------------------------------------------------------------------= ------ > > >> > joel's Profile: 229 > > >> > View this thread: > > >> >http://www.thecodecage.com/forumz/showthread.php?t=3D171628 > > > >> > [url=3D"http://www.thecodecage.com"]Microsoft Office Help[/url] > > > >> . > > > .- Skjul tekst i anf=F8rselstegn - > > - Vis tekst i anf=F8rselstegn -
|
|
0 |
|
Per
1/22/2010 3:44:07 AM
For counting or summing multiple columns use sumproduct. Something like this This if equivalent of a CountIF =sumproduct(--(A1:A100=5),--(B1:B100=D5)) The -- converter the True/False to 1/0. The is equivalent to Summing column C when columns A and B match the criteria =sumproduct(--(A1:A100=5),--(B1:B100=D5),C1:C100) -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=171628 [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
|
|
0 |
|
joel
1/22/2010 3:49:21 AM
The X's in table 2 need to be automatically populated from the X's in Table 1 using the project and phase columns as criteria. Table1 Project Phase 1 2 3 4 5 1 Initiation x x x 1 Construction x x x 2 Initiation x x 2 Construction x x x x Table 2 Project Phase Resource 1 2 3 4 5 1 Initiation Tom x x x 1 Initiation Joe x x x 2 Initiation Tom x x 2 Initiation Joe x x 1 Construction Tom x x x 1 Construction Joe x x x 2 Construction Tom x x x x 2 Construction Joe x x x x "Per Jessen" wrote: > HI > > I am confused now! > > You say that you want to 'copy' the result from the matching row !?! > > I have a feeling that a SUMPRODUCT formula can do what you need, but > to be sure, can you post some sample data, with an description of what > you want and the expected result. > > Regards, > Per > > On 22 Jan., 04:26, primed <pri...@discussions.microsoft.com> wrote: > > Correct, which makes the countif solution not workable. ie i have to search > > two colums for two criteria matches then copy the result from the matching > > row in column (Table1[2]). > > > > Table1[2] used to contain a number 1 it now contains an X or is blank. > > > > Any more suggestions? > > Your help much appreciated. > > > > Regards > > Primed > > > > > > > > "Bob Phillips" wrote: > > > COUNTIFS doesn't require a sumrange. > > > > > HTH > > > > > Bob > > > > > "primed" <pri...@discussions.microsoft.com> wrote in message > > >news:495E61B5-6539-4F23-B64D-5C7A656228CC@microsoft.com... > > > > Tried countif comes up with "too few arguments" > > > > I looked at the syntax of countifs and there doesnt appear to be an > > > > equivalent "sumrange", which in the formula is Table1[2]. > > > > > > Some additional info that you may require, > > > > Table1[2] contains either a X or is blank and there will only ever be 1 > > > > cell > > > > in the range Table1[2] that is a match. > > > > > > Any more ideas? > > > > > > Regards > > > > Primed > > > > > > Hi > > > > > > "Bob Phillips" wrote: > > > > > >> SUMIFS/COUNTIFS are Excel 2007 functions! > > > > > >> Bob > > > > > >> "joel" <joel.452...@thecodecage.com> wrote in message > > > >>news:joel.452wlb@thecodecage.com... > > > > > >> > Use Countif instead of Sumif > > > > > >> > from > > > > > >> > =SUMIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) > > > > > >> > to > > > > > >> > =CountIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) > > > > > >> > Do you really have a function Countifs with an "S" at the end? thie > > > >> > maybe an UDF that needs to be modified. > > > > > >> > -- > > > >> > joel > > > >> > ------------------------------------------------------------------------ > > > >> > joel's Profile: 229 > > > >> > View this thread: > > > >> >http://www.thecodecage.com/forumz/showthread.php?t=171628 > > > > > >> > [url="http://www.thecodecage.com"]Microsoft Office Help[/url] > > > > > >> . > > > > > .- Skjul tekst i anførselstegn - > > > > - Vis tekst i anførselstegn - > > . >
|
|
0 |
|
Utf
1/22/2010 5:30:01 AM
Hi, Tried the sumproduct formula, seems it only works on numbers and not letters. "joel" wrote: > > For counting or summing multiple columns use sumproduct. Something like > this > > This if equivalent of a CountIF > =sumproduct(--(A1:A100=5),--(B1:B100=D5)) > > > The -- converter the True/False to 1/0. > > > The is equivalent to Summing column C when columns A and B match the > criteria > > =sumproduct(--(A1:A100=5),--(B1:B100=D5),C1:C100) > > > -- > joel > ------------------------------------------------------------------------ > joel's Profile: 229 > View this thread: http://www.thecodecage.com/forumz/showthread.php?t=171628 > > [url="http://www.thecodecage.com"]Microsoft Office Help[/url] > > . >
|
|
0 |
|
Utf
1/22/2010 6:07:01 AM
Sumproduct will work on strings but you need to put the x's in double quotes and it is case sensitve =SUMProduct(--($A$11:$A$22=$A38),--($B$11:$B$22=$B38),--($C$11:$C$38="x"),--($D$11:$D$38="x"),--($E$11:$E$38="x"),--($F$11:$F$38="x"),--($G$11:$G$38="x")) -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=171628 [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
|
|
0 |
|
joel
1/22/2010 11:25:56 AM
I would use VBA
Public Sub ProcessData()
Dim i As Long, j As Long
Dim LastRow As Long
Dim NextRow As Long
Dim aryUsers As Variant
Dim NumUsers As Long
Dim sh As Worksheet
aryUsers = Array("Tom", "Joe")
Set sh = Worksheets("Sheet2")
With Worksheets("Sheet1")
.Rows(1).Copy sh.Range("A1")
sh.Columns("B").Insert
sh.Range("B1").Value = "Resource"
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
NextRow = 1
For i = 2 To LastRow
For j = LBound(aryUsers) To UBound(aryUsers)
NextRow = NextRow + 1
.Cells(i, "A").Copy sh.Cells(NextRow, "A")
sh.Cells(NextRow, "B").Value = aryUsers(j)
.Cells(i, "B").Resize(, 6).Copy sh.Cells(NextRow, "C")
Next j
Next i
End With
End Sub
HTH
Bob
"primed" <primed@discussions.microsoft.com> wrote in message
news:EC0243AA-A994-4571-9975-F74C62113403@microsoft.com...
> The X's in table 2 need to be automatically populated from the X's in
> Table 1
> using the project and phase columns as criteria.
>
>
> Table1
> Project Phase 1 2 3 4 5
> 1 Initiation x x x
> 1 Construction x x x
> 2 Initiation x x
> 2 Construction x x x x
>
> Table 2
> Project Phase Resource 1 2 3 4 5
> 1 Initiation Tom x x x
> 1 Initiation Joe x x x
> 2 Initiation Tom x x
> 2 Initiation Joe x x
> 1 Construction Tom x x x
> 1 Construction Joe x x x
> 2 Construction Tom x x x x
> 2 Construction Joe x x x x
>
>
>
> "Per Jessen" wrote:
>
>> HI
>>
>> I am confused now!
>>
>> You say that you want to 'copy' the result from the matching row !?!
>>
>> I have a feeling that a SUMPRODUCT formula can do what you need, but
>> to be sure, can you post some sample data, with an description of what
>> you want and the expected result.
>>
>> Regards,
>> Per
>>
>> On 22 Jan., 04:26, primed <pri...@discussions.microsoft.com> wrote:
>> > Correct, which makes the countif solution not workable. ie i have to
>> > search
>> > two colums for two criteria matches then copy the result from the
>> > matching
>> > row in column (Table1[2]).
>> >
>> > Table1[2] used to contain a number 1 it now contains an X or is blank.
>> >
>> > Any more suggestions?
>> > Your help much appreciated.
>> >
>> > Regards
>> > Primed
>> >
>> >
>> >
>> > "Bob Phillips" wrote:
>> > > COUNTIFS doesn't require a sumrange.
>> >
>> > > HTH
>> >
>> > > Bob
>> >
>> > > "primed" <pri...@discussions.microsoft.com> wrote in message
>> > >news:495E61B5-6539-4F23-B64D-5C7A656228CC@microsoft.com...
>> > > > Tried countif comes up with "too few arguments"
>> > > > I looked at the syntax of countifs and there doesnt appear to be an
>> > > > equivalent "sumrange", which in the formula is Table1[2].
>> >
>> > > > Some additional info that you may require,
>> > > > Table1[2] contains either a X or is blank and there will only ever
>> > > > be 1
>> > > > cell
>> > > > in the range Table1[2] that is a match.
>> >
>> > > > Any more ideas?
>> >
>> > > > Regards
>> > > > Primed
>> >
>> > > > Hi
>> >
>> > > > "Bob Phillips" wrote:
>> >
>> > > >> SUMIFS/COUNTIFS are Excel 2007 functions!
>> >
>> > > >> Bob
>> >
>> > > >> "joel" <joel.452...@thecodecage.com> wrote in message
>> > > >>news:joel.452wlb@thecodecage.com...
>> >
>> > > >> > Use Countif instead of Sumif
>> >
>> > > >> > from
>> >
>> > > >> > =SUMIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38)
>> >
>> > > >> > to
>> >
>> > > >> > =CountIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38)
>> >
>> > > >> > Do you really have a function Countifs with an "S" at the end?
>> > > >> > thie
>> > > >> > maybe an UDF that needs to be modified.
>> >
>> > > >> > --
>> > > >> > joel
>> > > >> > ------------------------------------------------------------------------
>> > > >> > joel's Profile: 229
>> > > >> > View this thread:
>> > > >> >http://www.thecodecage.com/forumz/showthread.php?t=171628
>> >
>> > > >> > [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
>> >
>> > > >> .
>> >
>> > > .- Skjul tekst i anf�rselstegn -
>> >
>> > - Vis tekst i anf�rselstegn -
>>
>> .
>>
|
|
0 |
|
Bob
1/22/2010 4:03:11 PM
Similar Artilces:
help moving to new exchange server
Hi i have hopefully a quick and easy question. we are in the process of migrating our exchange 5.5 server, to exchange 2003. what i need as a quick easy way to modify everyones outlook profile to point to the new server. the new server is in a whole new org, and with a new name. we are using outlook 98,2000,xp, and 2003 i'm leaning towards using the .prf files, but what i am hoping is that someone else has done this and has some scripts already made :) Thanks for any help anyone can give. Where do you get stuck with creating a prf-file? A better way to do it is by using the foll...
Help with a query 01-23-08
Hi All, I am sure there is a simple solution to this but my brain doesn't seem to be working at the moment. In my dataset one field has the value "Data Quaternity" and the other values are NULL (but could be another string) and I want to exclude any records from the dataset which have this value. I have tried to put the following expressions in the Criteria for the query. NOT "Data Quaternity" or NOT Like "Data Quaternity" But when I do this all I get is an empty data set. I can sort on IS NULL in the criteria now, but I don't want to do this as ...
Need Help! Changing country
I just moved from Canada to China. In Canada, when each new contact popup, the default long distance prefix is of Canada. But after backing up the Outlook folders and now moved to China, and restore the data to a new system, Outlook still insists that the default country for new contact is Canada. I've set the Control Panel -> Regional and Language Options -> Location to China, but with no effect on Outlook whatsoever. Please if anyone can help... Thanks You seem to have neglected to reset the default dialing location for the operating system. ------ Russ Valentine [MVP-Out...
sumif formula #2
At the moment I am trying to work a sumif formula The problem I am encountering is once I have set the range and criteri it won't pick up the sum range as the range I am specifying is fo example J15:CB45 - it will pick up J15:CB15 but I need it to pick u the whole range - do you know of anyway I can resolve this. My formula is =sumif($J$3:$CB$3,E$3,($J15:$CB45)) but it doesn't see to work this one does though =sumif($J$3:$CB$3,E$3,($J15:$CB15)) bu that doesn't help me! I have put a print screen of the spreadsheet I am trying to work on. Thanks Jenni -- Message posted from htt...
pivot table help!
I need some help with an issue I cannot seem to solve! I have a pivot table in Excel that is based off of a data input sheet. The data changes every month and the pivot table is always linked to the specific data tab. When the underlying data changes, the "old data" still displays as options to check in the pivot table. The refresh option does not seem to fix this, nor does the "Save data with table layout" option. I only want the options to check to be current data. For example, my data sheet only has data for July and August, but the pivot table has options to check...
Please help!! Microsoft Office Student and Teacher
My cousin and I started college this year, so we both needed Microsoft Office Student and Teacher, the 2007 version. When she bought her computer, the guy said that it had a couple activations, so that we both would be able to use it. He was wrong, since it was an OEM version, there was only one activation. The problem is I activated mine before we tried to do hers, so my Student and Teacher is activated using her number. So I was wondering if there was anyway to deactivate mine, and then activate hers using her number. ...
Macro Help
Hi All I have a workbook which contains >400 sheets. What I need to do is to insert a "cover" sheet and then create a macro to pick up the same four cells from each worksheet (B2, G9, B14 and B9) and copy and paste the values into the Cover Sheet going progressively down a column for each different sheet. so i need the macro to go to Sheet1 get the value from B2 and paste it on the cover sheet in A2, then get the value G9 and paste it on the cover sheet in B2 etc... then go to Sheet2 get the value from B2 and paste it on the cover sheet in A3, then get the value G9 and...
Help me #3
How can i change font size in combo box in exce -- raphy5 ----------------------------------------------------------------------- raphy50's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1644 View this thread: http://www.excelforum.com/showthread.php?threadid=27803 Hi what combo box do you mean. The one create with 'Data - Validation'? -- Regards Frank Kabel Frankfurt, Germany raphy50 wrote: > How can i change font size in combo box in excel For controls from the Forms toolbar, the font size can't be changed. If you use controls from the Co...
help..journal
Hello.. > > I am using OL 2000 (IMO)and have configured my journal to log certain > CONTACTS emails received and sent from my contact list. > > Somehow only certain contacts get listed as received and none are logged as > sent. I have placed a check next to the names I want logged.. but they are > not showing up in journal.. > > This is for emails only - not Word or Exel. > > How do I get emails to be logged for those I've chose to record?? > > help Please.. > Dave > > In Journal options, is the EMAIL option checked, as well as the me...
Need Help in numbering cells / auto fill
Ok im having bad day got Drain Bamage In say colunm A rows 1 tru 100 I want to number these cells 1 tru 100 in column C I want to number rows 1 tru 100 as 101 thru 200 by physically typing these numbers in each cell as data. A1 is 1 A2 is 2 A99 is 99 A100 is 100 C1 is 101 C100 is 200 I know that you can use auto fill or some thing so you dont have to type each number in. You first say type in 1 2 3 and then select a range and it auto fills it.. and inserts 4 thru 100 I hope you can understand this if not let me know Hi BostonBeaner In A1 enter 1 Drag the cell down...
Help with formula trowing a #value! result
Hi I've downloaded a mortgage calculator from the web, being anewbie I'm stuck with the following issue: When the last payment comes down to 0 or near 0 like 0.03 cents all the formulas values return a #VALUE!, I want that the value be displayed as 0. In example the maximum payments are 431 monthly payments, lets say the last payment the client made was for 378, now after that paymet I need the value to be displayed as 0 up to row 431, because he has finished his payments. A formula is if(F378+D378<C378,C378-(D378+F378),"") EVALUATES TO------>...
HELP!!!!!!!!! with a Hierachial Script and XML
SQL Gurus, I need help I have a table with categories Category1==>Root SubCategory1.1 SubCategory1.2 Sub-SubCategory1.2.1 Category2==>Root SubCategory2.1 SubCategory2.2 Sub-SubCategory2.2.1 .... and so on ... Next, I have a form (HTML) with roots as checkboxes i.e Category1, Category2, Category3 1. When the user checks Category1 and clicks retrieve, return all children of Category1 2. When the user checks Category1 and Category2 and clicks retrieve, return all children of Category1 and Category2 Below is my c# script: [WebMethod] public...
need help with histogram
Hi, Is it possible to make a histogram with multiple input ranges or does anyone know what other type of distribution graph I can use if this is not possible? I have the following data (price each person sold in each day): A B C D ...so forth 3/1/06 3/2/06 3/3/06 ...daily date until end of month John .23 .15 .20 Mary .18 .10 .20 Bob .15 .15 .19 Kate .23 .16 .18 Ann .23 .15 .18 ... to row 282 I would like to know how ma...
Offline Backup Help
I am running Veritas Backup Exec 9.1 with the Exchange Server Plug-in on Exchange Server 2003. I need so help to create a offline backup. I was told the there was a how to on Veritas.com but could not find the article. Can someone point me in the right direction. The KB that I found on MS site was way more indepth than what I have heard needs to be done. Thanks I do you want to do an offline backup, this is not recommended. Performing an offline backup is simply stopping all the Exchange services and then copying the database and log files. -- Mark Fugatt "cbdrako99" &l...
sumif across multiple sheets in excel 2007 based on a condition
I have a small work book, tracks deliveries made and trucks used. I have seven sheets Saturday thru Friday and one sheet that totals everything up! The weekly sheets have 34 rows representing 34 stores that we service and the total sheet also has 34 rows that contain the totals for that week. Here is my question I am trying to, on the totals sheet, sum up the total trucks that we used per store per week. In the cell would be entered 0, 0.5, 1, 2 and so on depending on how many trucks we used for that day. Yes we use 0.5 if the store was combo'ed with another store. I ne...
Need help with ftp script
I'm trying to create a WinXP ftp script and I'm having a hard time even figuring out the syntax. I have an XML file on one of my websites, let's call it sourceweb.com (publicly available) that I want to copy to a directory on another of my websites, let's call it destinationweb.com, so that it overwrites the existing file. I'd like the script to include the destination's ftp user name & prompt me for the password. Can anyone please show me how to do this? Thanks!!! "Toni" <Toni@nowhere.com> wrote in message news:OOmjkD...
Help!larger than A4 brochure that folds in half & across 3 times
I want to be able to fold the paper in half and then proceed with a standard three column brochure that would have information on all sides. I cannot find and template in publisher. Not sure what you are trying to do. Setup your page as a booklet, in the arrange menu setup 3 columns. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Aine Mc Laughlin" <AineMcLaughlin@discussions.microsoft.com> wrote in message news:D2FACA3F-D569-445A-AC97-85371675F3CE@microsoft.com... >I want to be able to fold the paper in half ...
SUMIF or SUMIFS
Here is what I have Col A Col B Col C 1310 3 3,463.00 1315 3 740 1330 3 1369 3 -178 1375 3 -105 1640 3 110 135 4 1310 4 1,460.00 1315 4 1,521.00 1375 4 -65 1310 6 3,284.96 I am trying to figure out a way to add column C to a new cell if Column A is between 1310 and 1369. Any suggestions?? =if(and(A1>1310;A1<1369);C1;"") HTH "Jeff" wrote: > Here is what I have > > > Col A Col B Col C > 1310 3 3,463.00 > 1315 3 740 > 1330 3 > 1369 3 -178 > 1375 3 -105 > 1640 3 110 > 135 4 > 1310 4 1,460.00 >...
Error PLEASE HELP!!!
I'm trying to syudi Ansi C in this program like suggested by my University Book, #include "stdafx.h" #include <stdio.h> #include <string.h> #include <stdlib.h> #define MAXLINES 5000 /* max #lines to be sorted */ char *lineptr[MAXLINES]; /* pointers to text lines */ int readlines(char *lineptr[], int nlines); void writelines(char *lineptr[], int nlines); void qsort(void *lineptr[], int left, int right, int (*comp)(void *, void *)); int numcmp(char *, char *); int StrCmp(char *, char *); /* sort input lin...
Chart repeats data when printed pls help !
I have a spreadsheet that consists of a long list of data on the left with a simple x-y scatter chart on the right - both list and chart are longer than one page, and therefore must be printed across several pages. When I print the spreadsheet, the chart on each page prints the first two rows of data from the beginning of the chart, so for example if the data runs from 1-100, the first page chart printout shows 1-20, the second page shows 1-2 then 13-20, the third page shows 1-2 then 23-30. Could it be something to do with excel printing out the x axis on each page, given that the x axis is ...
Offline defrag for exchange 2003, Help
I have a 2003 exchange server running on 2003 Server. I have reached the 15 gig limit of Exchange I have cleaned out as much mail as I can buy still need space . How do you do a offline defrag of the .EDB files???? I know how to do a defrag for exchange 2000 but the commands do not work.... PLease help.... Here is a link that explains how to do it: http://support.microsoft.com/default.aspx?scid=kb;en-us;254132&Product=exch2003 -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Hlobell" <anonymous@discussions.microsoft.com> wrote in...
another sumifs plea
I'm struggling to convert a sumifs line from 2007 to excel 2003. The line I have working correctly in 2007 is: =SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC Rental'!$L$4:$L$21,">28/2/2010")-SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC Rental'!$L$4:$L$21,">31/3/2010") Can any one please help me to put this into 2003? Best Regards Dave =SUMPRODUCT('Washing MC Rental'!$K$4:$K$21,...
help with SQL Join
I need to use the Vendor Transaction Table to identify which vendors have NOT been used in 2008 or 2009. This table has one row for every transaction. I know how to get the vendors that were active during 2008 or 2009: SELECT DISTINCT vendor FROM Vendor_Transaction_Table WHERE year in ( 2008, 2009) Is there a way to join the table to itself and identify which vendors have not had a transaction in 2008 or 2009? Or is there a better approach than a joining it to itself ? Dig Dig wrote: > I need to use the Vendor Transaction Table to identify which vendors > have NOT bee...
Font and size help
In Excel 2007, I am updating a worksheet developed by someone else. I can't get a column of email addresses to display times new roman, 12. I have reset the page default, I have formatted just the column containing the email addresses and nothing seems to work. After entering an email address, it appears as ms sans serif, 10 with an underline (ie link address) I then change the individual cell to times ne roman, 12 and it is properly retained. Help appreciated. thanks Hi. This is due to your autocorrect options in Excel. To stop Excel taking over your formatting try the following:...
Please Help...
Hi, Basically, I've made a BIG mistake. I entered my own PERSONAL e-mail address during setup of CRM 3.0 on the server, as i could not create a new user called CRMMail at the time. (The add new user wizard keeps crashing). So now, all my personal mail gets removed by CRM upon arrival on my inbox. I can retrieve it using recover deleted items menu in outlook, but have to quickly go offline, and copy it before CRM grabs it. So, I need to change the settings of CRM so that it uses a Different e-mail account, i.e. CRMMail that I have now created after a re-start. Uninstall/Reinstall....
Hi i have hopefully a quick and easy question. we are in the process of migrating our exchange 5.5 server, to exchange 2003. what i need as a quick easy way to modify everyones outlook profile to point to the new server. the new server is in a whole new org, and with a new name. we are using outlook 98,2000,xp, and 2003 i'm leaning towards using the .prf files, but what i am hoping is that someone else has done this and has some scripts already made :) Thanks for any help anyone can give. Where do you get stuck with creating a prf-file? A better way to do it is by using the foll...
Help with a query 01-23-08
Hi All, I am sure there is a simple solution to this but my brain doesn't seem to be working at the moment. In my dataset one field has the value "Data Quaternity" and the other values are NULL (but could be another string) and I want to exclude any records from the dataset which have this value. I have tried to put the following expressions in the Criteria for the query. NOT "Data Quaternity" or NOT Like "Data Quaternity" But when I do this all I get is an empty data set. I can sort on IS NULL in the criteria now, but I don't want to do this as ...
Need Help! Changing country
I just moved from Canada to China. In Canada, when each new contact popup, the default long distance prefix is of Canada. But after backing up the Outlook folders and now moved to China, and restore the data to a new system, Outlook still insists that the default country for new contact is Canada. I've set the Control Panel -> Regional and Language Options -> Location to China, but with no effect on Outlook whatsoever. Please if anyone can help... Thanks You seem to have neglected to reset the default dialing location for the operating system. ------ Russ Valentine [MVP-Out...
sumif formula #2
At the moment I am trying to work a sumif formula The problem I am encountering is once I have set the range and criteri it won't pick up the sum range as the range I am specifying is fo example J15:CB45 - it will pick up J15:CB15 but I need it to pick u the whole range - do you know of anyway I can resolve this. My formula is =sumif($J$3:$CB$3,E$3,($J15:$CB45)) but it doesn't see to work this one does though =sumif($J$3:$CB$3,E$3,($J15:$CB15)) bu that doesn't help me! I have put a print screen of the spreadsheet I am trying to work on. Thanks Jenni -- Message posted from htt...
pivot table help!
I need some help with an issue I cannot seem to solve! I have a pivot table in Excel that is based off of a data input sheet. The data changes every month and the pivot table is always linked to the specific data tab. When the underlying data changes, the "old data" still displays as options to check in the pivot table. The refresh option does not seem to fix this, nor does the "Save data with table layout" option. I only want the options to check to be current data. For example, my data sheet only has data for July and August, but the pivot table has options to check...
Please help!! Microsoft Office Student and Teacher
My cousin and I started college this year, so we both needed Microsoft Office Student and Teacher, the 2007 version. When she bought her computer, the guy said that it had a couple activations, so that we both would be able to use it. He was wrong, since it was an OEM version, there was only one activation. The problem is I activated mine before we tried to do hers, so my Student and Teacher is activated using her number. So I was wondering if there was anyway to deactivate mine, and then activate hers using her number. ...
Macro Help
Hi All I have a workbook which contains >400 sheets. What I need to do is to insert a "cover" sheet and then create a macro to pick up the same four cells from each worksheet (B2, G9, B14 and B9) and copy and paste the values into the Cover Sheet going progressively down a column for each different sheet. so i need the macro to go to Sheet1 get the value from B2 and paste it on the cover sheet in A2, then get the value G9 and paste it on the cover sheet in B2 etc... then go to Sheet2 get the value from B2 and paste it on the cover sheet in A3, then get the value G9 and...
Help me #3
How can i change font size in combo box in exce -- raphy5 ----------------------------------------------------------------------- raphy50's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1644 View this thread: http://www.excelforum.com/showthread.php?threadid=27803 Hi what combo box do you mean. The one create with 'Data - Validation'? -- Regards Frank Kabel Frankfurt, Germany raphy50 wrote: > How can i change font size in combo box in excel For controls from the Forms toolbar, the font size can't be changed. If you use controls from the Co...
help..journal
Hello.. > > I am using OL 2000 (IMO)and have configured my journal to log certain > CONTACTS emails received and sent from my contact list. > > Somehow only certain contacts get listed as received and none are logged as > sent. I have placed a check next to the names I want logged.. but they are > not showing up in journal.. > > This is for emails only - not Word or Exel. > > How do I get emails to be logged for those I've chose to record?? > > help Please.. > Dave > > In Journal options, is the EMAIL option checked, as well as the me...
Need Help in numbering cells / auto fill
Ok im having bad day got Drain Bamage In say colunm A rows 1 tru 100 I want to number these cells 1 tru 100 in column C I want to number rows 1 tru 100 as 101 thru 200 by physically typing these numbers in each cell as data. A1 is 1 A2 is 2 A99 is 99 A100 is 100 C1 is 101 C100 is 200 I know that you can use auto fill or some thing so you dont have to type each number in. You first say type in 1 2 3 and then select a range and it auto fills it.. and inserts 4 thru 100 I hope you can understand this if not let me know Hi BostonBeaner In A1 enter 1 Drag the cell down...
Help with formula trowing a #value! result
Hi I've downloaded a mortgage calculator from the web, being anewbie I'm stuck with the following issue: When the last payment comes down to 0 or near 0 like 0.03 cents all the formulas values return a #VALUE!, I want that the value be displayed as 0. In example the maximum payments are 431 monthly payments, lets say the last payment the client made was for 378, now after that paymet I need the value to be displayed as 0 up to row 431, because he has finished his payments. A formula is if(F378+D378<C378,C378-(D378+F378),"") EVALUATES TO------>...
HELP!!!!!!!!! with a Hierachial Script and XML
SQL Gurus, I need help I have a table with categories Category1==>Root SubCategory1.1 SubCategory1.2 Sub-SubCategory1.2.1 Category2==>Root SubCategory2.1 SubCategory2.2 Sub-SubCategory2.2.1 .... and so on ... Next, I have a form (HTML) with roots as checkboxes i.e Category1, Category2, Category3 1. When the user checks Category1 and clicks retrieve, return all children of Category1 2. When the user checks Category1 and Category2 and clicks retrieve, return all children of Category1 and Category2 Below is my c# script: [WebMethod] public...
need help with histogram
Hi, Is it possible to make a histogram with multiple input ranges or does anyone know what other type of distribution graph I can use if this is not possible? I have the following data (price each person sold in each day): A B C D ...so forth 3/1/06 3/2/06 3/3/06 ...daily date until end of month John .23 .15 .20 Mary .18 .10 .20 Bob .15 .15 .19 Kate .23 .16 .18 Ann .23 .15 .18 ... to row 282 I would like to know how ma...
Offline Backup Help
I am running Veritas Backup Exec 9.1 with the Exchange Server Plug-in on Exchange Server 2003. I need so help to create a offline backup. I was told the there was a how to on Veritas.com but could not find the article. Can someone point me in the right direction. The KB that I found on MS site was way more indepth than what I have heard needs to be done. Thanks I do you want to do an offline backup, this is not recommended. Performing an offline backup is simply stopping all the Exchange services and then copying the database and log files. -- Mark Fugatt "cbdrako99" &l...
sumif across multiple sheets in excel 2007 based on a condition
I have a small work book, tracks deliveries made and trucks used. I have seven sheets Saturday thru Friday and one sheet that totals everything up! The weekly sheets have 34 rows representing 34 stores that we service and the total sheet also has 34 rows that contain the totals for that week. Here is my question I am trying to, on the totals sheet, sum up the total trucks that we used per store per week. In the cell would be entered 0, 0.5, 1, 2 and so on depending on how many trucks we used for that day. Yes we use 0.5 if the store was combo'ed with another store. I ne...
Need help with ftp script
I'm trying to create a WinXP ftp script and I'm having a hard time even figuring out the syntax. I have an XML file on one of my websites, let's call it sourceweb.com (publicly available) that I want to copy to a directory on another of my websites, let's call it destinationweb.com, so that it overwrites the existing file. I'd like the script to include the destination's ftp user name & prompt me for the password. Can anyone please show me how to do this? Thanks!!! "Toni" <Toni@nowhere.com> wrote in message news:OOmjkD...
Help!larger than A4 brochure that folds in half & across 3 times
I want to be able to fold the paper in half and then proceed with a standard three column brochure that would have information on all sides. I cannot find and template in publisher. Not sure what you are trying to do. Setup your page as a booklet, in the arrange menu setup 3 columns. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Aine Mc Laughlin" <AineMcLaughlin@discussions.microsoft.com> wrote in message news:D2FACA3F-D569-445A-AC97-85371675F3CE@microsoft.com... >I want to be able to fold the paper in half ...
SUMIF or SUMIFS
Here is what I have Col A Col B Col C 1310 3 3,463.00 1315 3 740 1330 3 1369 3 -178 1375 3 -105 1640 3 110 135 4 1310 4 1,460.00 1315 4 1,521.00 1375 4 -65 1310 6 3,284.96 I am trying to figure out a way to add column C to a new cell if Column A is between 1310 and 1369. Any suggestions?? =if(and(A1>1310;A1<1369);C1;"") HTH "Jeff" wrote: > Here is what I have > > > Col A Col B Col C > 1310 3 3,463.00 > 1315 3 740 > 1330 3 > 1369 3 -178 > 1375 3 -105 > 1640 3 110 > 135 4 > 1310 4 1,460.00 >...
Error PLEASE HELP!!!
I'm trying to syudi Ansi C in this program like suggested by my University Book, #include "stdafx.h" #include <stdio.h> #include <string.h> #include <stdlib.h> #define MAXLINES 5000 /* max #lines to be sorted */ char *lineptr[MAXLINES]; /* pointers to text lines */ int readlines(char *lineptr[], int nlines); void writelines(char *lineptr[], int nlines); void qsort(void *lineptr[], int left, int right, int (*comp)(void *, void *)); int numcmp(char *, char *); int StrCmp(char *, char *); /* sort input lin...
Chart repeats data when printed pls help !
I have a spreadsheet that consists of a long list of data on the left with a simple x-y scatter chart on the right - both list and chart are longer than one page, and therefore must be printed across several pages. When I print the spreadsheet, the chart on each page prints the first two rows of data from the beginning of the chart, so for example if the data runs from 1-100, the first page chart printout shows 1-20, the second page shows 1-2 then 13-20, the third page shows 1-2 then 23-30. Could it be something to do with excel printing out the x axis on each page, given that the x axis is ...
Offline defrag for exchange 2003, Help
I have a 2003 exchange server running on 2003 Server. I have reached the 15 gig limit of Exchange I have cleaned out as much mail as I can buy still need space . How do you do a offline defrag of the .EDB files???? I know how to do a defrag for exchange 2000 but the commands do not work.... PLease help.... Here is a link that explains how to do it: http://support.microsoft.com/default.aspx?scid=kb;en-us;254132&Product=exch2003 -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Hlobell" <anonymous@discussions.microsoft.com> wrote in...
another sumifs plea
I'm struggling to convert a sumifs line from 2007 to excel 2003. The line I have working correctly in 2007 is: =SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC Rental'!$L$4:$L$21,">28/2/2010")-SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC Rental'!$L$4:$L$21,">31/3/2010") Can any one please help me to put this into 2003? Best Regards Dave =SUMPRODUCT('Washing MC Rental'!$K$4:$K$21,...
help with SQL Join
I need to use the Vendor Transaction Table to identify which vendors have NOT been used in 2008 or 2009. This table has one row for every transaction. I know how to get the vendors that were active during 2008 or 2009: SELECT DISTINCT vendor FROM Vendor_Transaction_Table WHERE year in ( 2008, 2009) Is there a way to join the table to itself and identify which vendors have not had a transaction in 2008 or 2009? Or is there a better approach than a joining it to itself ? Dig Dig wrote: > I need to use the Vendor Transaction Table to identify which vendors > have NOT bee...
Font and size help
In Excel 2007, I am updating a worksheet developed by someone else. I can't get a column of email addresses to display times new roman, 12. I have reset the page default, I have formatted just the column containing the email addresses and nothing seems to work. After entering an email address, it appears as ms sans serif, 10 with an underline (ie link address) I then change the individual cell to times ne roman, 12 and it is properly retained. Help appreciated. thanks Hi. This is due to your autocorrect options in Excel. To stop Excel taking over your formatting try the following:...
Please Help...
Hi, Basically, I've made a BIG mistake. I entered my own PERSONAL e-mail address during setup of CRM 3.0 on the server, as i could not create a new user called CRMMail at the time. (The add new user wizard keeps crashing). So now, all my personal mail gets removed by CRM upon arrival on my inbox. I can retrieve it using recover deleted items menu in outlook, but have to quickly go offline, and copy it before CRM grabs it. So, I need to change the settings of CRM so that it uses a Different e-mail account, i.e. CRMMail that I have now created after a re-start. Uninstall/Reinstall....