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?

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

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



.

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



.

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



.



.

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 -

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

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 -

.

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

.

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

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 -

.