sumifs help - microsoft.public.excel.programming

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]

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] >

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] > > > > > . >

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] >> > >> >> >> . >>

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] > >> > > >> > >> > >> . > >> > > > . >

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 -

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]

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 - > > . >

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] > > . >

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]

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 - >> >> . >>

Data Validation. Multiple.

170

shapper

Copy named range, but skip blanks

319

Utf

X = SHELL

118

Utf

Data in a Calender layout view

162

Utf

Delete all rows not current date

117

Utf

Please assist. Extract Number from String

183

Damil4real

Populating combox with worksheet names

198

Utf

Making a frame visible or not using a command button

207

Utf

Changing page height while in a multipage

111

Utf

Need help working with XML data from another data source.

132

Utf

VBA 2 Codes

144

Utf

Copypicture from Excel to PowerPoint

239

Utf

is it possible to convert pdf file into excel?

103

yogi

form combobox auto expand

183

Avi

Macro for merging rows

230

Utf

8/4/2012 2:50:10 AM