sumifs help

[primed]

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?

 

[joel]

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? thi
maybe an UDF that needs to be modified

 

[Bob Phillips]

SUMIFS/COUNTIFS are Excel 2007 functions!

Bob

 

[primed]

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

SUMIFS/COUNTIFS are Excel 2007 functions!

Bob

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

Microsoft Office Help

.

 

[Bob Phillips]

COUNTIFS doesn't require a sumrange.

HTH

Bob

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

SUMIFS/COUNTIFS are Excel 2007 functions!

Bob

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

Microsoft Office Help

.

 

[primed]

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

COUNTIFS doesn't require a sumrange.

HTH

Bob

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

SUMIFS/COUNTIFS are Excel 2007 functions!

Bob


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

Microsoft Office Help



.

.

 

[Per Jessen]

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

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

COUNTIFS doesn't require a sumrange.

HTH

Bob

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 be1
cell
in the range Table1[2] that is a match.
Any more ideas?
Regards
Primed
Hi
:
SUMIFS/COUNTIFS are Excel 2007 functions!
Bob

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
Microsoft Office Help
.

.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

 

[joel]

For counting or summing multiple columns use sumproduct. Something lik
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 th
criteria

=sumproduct(--(A1:A100=5),--(B1:B100=D5),C1:C100

 

[primed]

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

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

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

COUNTIFS doesn't require a sumrange.

HTH

Bob

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?


"Bob Phillips" wrote:

SUMIFS/COUNTIFS are Excel 2007 functions!

news:[email protected]...

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.

Microsoft Office Help

.

.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

.

 

[primed]

Hi, Tried the sumproduct formula, seems it only works on numbers and not
letters.

 

[joel]

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"))

 

[Bob Phillips]

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

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

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

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



:
COUNTIFS doesn't require a sumrange.

HTH

Bob

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

:

SUMIFS/COUNTIFS are Excel 2007 functions!

Bob


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

Microsoft Office Help

.

.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

.