MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### sumifs help

• Follow

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

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

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

```
 0

```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
>
> [url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]
>

```
 0

```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
> >
> > [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
> >
>
>
> .
>
```
 0

```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
>> >
>> > [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
>> >
>>
>>
>> .
>>

```
 0

```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?

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:
> >> >
> >> > [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
> >> >
> >>
> >>
> >> .
> >>
>
>
> .
>
```
 0

```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?
>
> 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:
>
> > >> > [url=3D"http://www.thecodecage.com"]Microsoft Office Help[/url]
>
> > >> .
>
> > .- Skjul tekst i anf=F8rselstegn -
>
> - Vis tekst i anf=F8rselstegn -

```
 0

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

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

```
 0

```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:
> >
> > > >> > [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
> >
> > > >> .
> >
> > > .- Skjul tekst i anførselstegn -
> >
> > - Vis tekst i anførselstegn -
>
> .
>
```
 0

```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
>
> [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
>
> .
>
```
 0

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

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

```
 0

```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:
>> >
>> > > >> > [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
>> >
>> > > >> .
>> >
>> > > .- Skjul tekst i anf�rselstegn -
>> >
>> > - Vis tekst i anf�rselstegn -
>>
>> .
>>

```
 0

11 Replies
261 Views

8/4/2012 2:50:10 AM