|
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? |
sumifs help
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/sh...d.php?t=171628 Microsoft Office Help |
sumifs help
SUMIFS/COUNTIFS are Excel 2007 functions!
Bob "joel" wrote in message ... 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/sh...d.php?t=171628 Microsoft Office Help |
sumifs help
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" wrote in message ... 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/sh...d.php?t=171628 Microsoft Office Help . |
sumifs help
COUNTIFS doesn't require a sumrange.
HTH Bob "primed" wrote in message ... 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" wrote in message ... 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/sh...d.php?t=171628 Microsoft Office Help . |
sumifs help
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" wrote in message ... 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" wrote in message ... 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/sh...d.php?t=171628 Microsoft Office Help . . |
sumifs help
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 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" wrote in message ... 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" wrote in message ... 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/sh...d.php?t=171628 Microsoft Office Help . .- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
sumifs help
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/sh...d.php?t=171628 Microsoft Office Help |
sumifs help
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 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" wrote in message ... 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" wrote in message ... 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/sh...d.php?t=171628 Microsoft Office Help . .- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - . |
sumifs help
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/sh...d.php?t=171628 Microsoft Office Help . |
sumifs help
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/sh...d.php?t=171628 Microsoft Office Help |
sumifs help
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" wrote in message ... 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 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" wrote in message ... 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" wrote in message ... 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/sh...d.php?t=171628 Microsoft Office Help . .- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - . |
| All times are GMT +1. The time now is 11:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com