+ Reply to Thread
Results 1 to 8 of 8

Sumif based on column A and title of another column

  1. #1
    Rusty
    Guest

    Sumif based on column A and title of another column

    I am trying to create a formula to calculate the total amount of tests cases.
    The formula I have created so far is this
    =SUMIF('Test Cases'!A2:C300,"=<2K3 TermServ*",'Test Cases'!C2:C300)
    This works if the column I am referencing my data from is Column C
    (Sum_Range). Unfortunately this data will not always remain in column C so I
    want to add a criteria that says the Sum_Range will be the column where the
    word <total> is in the top row (Row 1)
    Note: This autosum is completed on a seperate tab, hence the 'Test Cases'
    in the SUMIF statement.
    Excel 2003 SP2

  2. #2
    Biff
    Guest

    Re: Sumif based on column A and title of another column

    Hi!

    Try this:

    =SUMIF('Test cases'!A2:A300,"=<2K3 TermServ*",OFFSET('Test
    cases'!A1,1,MATCH("total",'Test cases'!A1:IV1,0)-1,ROWS('Test
    cases'!A2:A300)))

    Biff

    "Rusty" <[email protected]> wrote in message
    news:[email protected]...
    >I am trying to create a formula to calculate the total amount of tests
    >cases.
    > The formula I have created so far is this
    > =SUMIF('Test Cases'!A2:C300,"=<2K3 TermServ*",'Test Cases'!C2:C300)
    > This works if the column I am referencing my data from is Column C
    > (Sum_Range). Unfortunately this data will not always remain in column C so
    > I
    > want to add a criteria that says the Sum_Range will be the column where
    > the
    > word <total> is in the top row (Row 1)
    > Note: This autosum is completed on a seperate tab, hence the 'Test Cases'
    > in the SUMIF statement.
    > Excel 2003 SP2




  3. #3
    Peo Sjoblom
    Guest

    Re: Sumif based on column A and title of another column

    I don't see how that can work, what is =<2K3 TermServ* ?

    less than and equal to is written <= in Excel

    You can't name a cell with an asterisk so it would mean text
    so greater than should be there, now if you want to check for
    something that starts with 2K3 TermServ where the asterisk is a wildcard why
    the <?

    Assume you want to sum you need to use the same size in SUMIF so you can
    never use
    A2:C300, sumif will only find the conditions in A2:A300, so assume you want
    to look for
    "2K3 TermServ*" in A2:A300 and sum values in the column that has a header in
    row i named "Total"

    =SUMIF('Test Cases'!$A$2:$A$300,"2K3 TermServ*",INDEX('Test
    Cases'!$C$2:$Z$300,0,MATCH("Total",'Test Cases'!$C$1:$Z$1,0)))

    I used up to column Z



    --
    Regards,

    Peo Sjoblom

    (No private emails please)


    "Rusty" <[email protected]> wrote in message
    news:[email protected]...
    >I am trying to create a formula to calculate the total amount of tests
    >cases.
    > The formula I have created so far is this
    > =SUMIF('Test Cases'!A2:C300,"=<2K3 TermServ*",'Test Cases'!C2:C300)
    > This works if the column I am referencing my data from is Column C
    > (Sum_Range). Unfortunately this data will not always remain in column C so
    > I
    > want to add a criteria that says the Sum_Range will be the column where
    > the
    > word <total> is in the top row (Row 1)
    > Note: This autosum is completed on a seperate tab, hence the 'Test Cases'
    > in the SUMIF statement.
    > Excel 2003 SP2



  4. #4
    Peo Sjoblom
    Guest

    Re: Sumif based on column A and title of another column

    I meant greater than shouldn't be there, of course it might be part of the
    name what do I know

    --
    Regards,

    Peo Sjoblom

    (No private emails please)


    "Peo Sjoblom" <[email protected]> wrote in message
    news:eOk%[email protected]...
    >I don't see how that can work, what is =<2K3 TermServ* ?
    >
    > less than and equal to is written <= in Excel
    >
    > You can't name a cell with an asterisk so it would mean text
    > so greater than should be there, now if you want to check for
    > something that starts with 2K3 TermServ where the asterisk is a wildcard
    > why the <?
    >
    > Assume you want to sum you need to use the same size in SUMIF so you can
    > never use
    > A2:C300, sumif will only find the conditions in A2:A300, so assume you
    > want to look for
    > "2K3 TermServ*" in A2:A300 and sum values in the column that has a header
    > in row i named "Total"
    >
    > =SUMIF('Test Cases'!$A$2:$A$300,"2K3 TermServ*",INDEX('Test
    > Cases'!$C$2:$Z$300,0,MATCH("Total",'Test Cases'!$C$1:$Z$1,0)))
    >
    > I used up to column Z
    >
    >
    >
    > --
    > Regards,
    >
    > Peo Sjoblom
    >
    > (No private emails please)
    >
    >
    > "Rusty" <[email protected]> wrote in message
    > news:[email protected]...
    >>I am trying to create a formula to calculate the total amount of tests
    >>cases.
    >> The formula I have created so far is this
    >> =SUMIF('Test Cases'!A2:C300,"=<2K3 TermServ*",'Test Cases'!C2:C300)
    >> This works if the column I am referencing my data from is Column C
    >> (Sum_Range). Unfortunately this data will not always remain in column C
    >> so I
    >> want to add a criteria that says the Sum_Range will be the column where
    >> the
    >> word <total> is in the top row (Row 1)
    >> Note: This autosum is completed on a seperate tab, hence the 'Test
    >> Cases'
    >> in the SUMIF statement.
    >> Excel 2003 SP2

    >



  5. #5
    Biff
    Guest

    Re: Sumif based on column A and title of another column

    >I don't see how that can work, what is =<2K3 TermServ* ?

    I had the same reaction but the OP says: This works if ...........

    Biff

    "Peo Sjoblom" <[email protected]> wrote in message
    news:eOk%[email protected]...
    >I don't see how that can work, what is =<2K3 TermServ* ?
    >
    > less than and equal to is written <= in Excel
    >
    > You can't name a cell with an asterisk so it would mean text
    > so greater than should be there, now if you want to check for
    > something that starts with 2K3 TermServ where the asterisk is a wildcard
    > why the <?
    >
    > Assume you want to sum you need to use the same size in SUMIF so you can
    > never use
    > A2:C300, sumif will only find the conditions in A2:A300, so assume you
    > want to look for
    > "2K3 TermServ*" in A2:A300 and sum values in the column that has a header
    > in row i named "Total"
    >
    > =SUMIF('Test Cases'!$A$2:$A$300,"2K3 TermServ*",INDEX('Test
    > Cases'!$C$2:$Z$300,0,MATCH("Total",'Test Cases'!$C$1:$Z$1,0)))
    >
    > I used up to column Z
    >
    >
    >
    > --
    > Regards,
    >
    > Peo Sjoblom
    >
    > (No private emails please)
    >
    >
    > "Rusty" <[email protected]> wrote in message
    > news:[email protected]...
    >>I am trying to create a formula to calculate the total amount of tests
    >>cases.
    >> The formula I have created so far is this
    >> =SUMIF('Test Cases'!A2:C300,"=<2K3 TermServ*",'Test Cases'!C2:C300)
    >> This works if the column I am referencing my data from is Column C
    >> (Sum_Range). Unfortunately this data will not always remain in column C
    >> so I
    >> want to add a criteria that says the Sum_Range will be the column where
    >> the
    >> word <total> is in the top row (Row 1)
    >> Note: This autosum is completed on a seperate tab, hence the 'Test
    >> Cases'
    >> in the SUMIF statement.
    >> Excel 2003 SP2

    >




  6. #6
    Rusty
    Guest

    Re: Sumif based on column A and title of another column

    My excel sheet is like this
    Test Cases tab
    column A - Column B - Column C -
    Column D - Column E
    Name - Failed - No Run -
    Not Completed - <total>
    <2K3 TermServ 2k Blah 1> -1 - - -1
    <2K3 TermServ 2k Blah 2> - - 1 - 1
    <2K3 TermServ 2k Blah 3> - - 1 - 1
    <2K3 TermServ 2k Blah 4> 1 1
    AD2000 2k Blah 1 4 4
    AD2000 2k Blah 2 2 2
    AD2000 2k Blah 3 1 1
    AD2000 2k Blah 4 1 1

    What I want is a total (The last column - column E at the moment) number of
    tests for <2k3 TermServ*. The autosum that I had checks the first column for
    the name, and if it matches the criteria, it adds the numbers from column C.
    The problem is that my "TOTAL" column will move from column C to column E and
    to other columns as well depending on how the testing is progressing. I want
    it to be able to find the column with "Total" as the title and then add up
    those numbers (dependant on the name in Column A). I hope that explains it a
    bit better. BTW, Thanks Biff but that sum didn't work either. I got a N/A
    result.

  7. #7
    Biff
    Guest

    Re: Sumif based on column A and title of another column

    Hi!

    Try this:

    =SUMPRODUCT(--(ISNUMBER(SEARCH("<2K3 TermServ",'Test
    cases'!A2:A10))),INDEX('Test cases'!A2:F10,,MATCH("total",'Test
    cases'!A1:F1,0)))

    Adjust ranges to suit.

    Biff

    "Rusty" <[email protected]> wrote in message
    news:[email protected]...
    > My excel sheet is like this
    > Test Cases tab
    > column A - Column B - Column C -
    > Column D - Column E
    > Name - Failed - No Run -
    > Not Completed - <total>
    > <2K3 TermServ 2k Blah 1> -1 - - -1
    > <2K3 TermServ 2k Blah 2> - - 1 - 1
    > <2K3 TermServ 2k Blah 3> - - 1 - 1
    > <2K3 TermServ 2k Blah 4> 1 1
    > AD2000 2k Blah 1 4 4
    > AD2000 2k Blah 2 2 2
    > AD2000 2k Blah 3 1 1
    > AD2000 2k Blah 4 1 1
    >
    > What I want is a total (The last column - column E at the moment) number
    > of
    > tests for <2k3 TermServ*. The autosum that I had checks the first column
    > for
    > the name, and if it matches the criteria, it adds the numbers from column
    > C.
    > The problem is that my "TOTAL" column will move from column C to column E
    > and
    > to other columns as well depending on how the testing is progressing. I
    > want
    > it to be able to find the column with "Total" as the title and then add up
    > those numbers (dependant on the name in Column A). I hope that explains
    > it a
    > bit better. BTW, Thanks Biff but that sum didn't work either. I got a
    > N/A
    > result.




  8. #8
    Rusty
    Guest

    Re: Sumif based on column A and title of another column

    Biff,
    Your a legend! Thank you very much. A bit of tweaking and it works like a
    charm.

    =SUMPRODUCT(--(ISNUMBER(SEARCH("<2K3 TermServ",'Test
    Cases'!A2:A300))),INDEX('Test Cases'!A2:G300,,MATCH("<total>",A1:F1,0)))

    Thanks

    Rusty

    "Biff" wrote:

    > Hi!
    >
    > Try this:
    >
    > =SUMPRODUCT(--(ISNUMBER(SEARCH("<2K3 TermServ",'Test
    > cases'!A2:A10))),INDEX('Test cases'!A2:F10,,MATCH("total",'Test
    > cases'!A1:F1,0)))
    >
    > Adjust ranges to suit.
    >
    > Biff
    >
    > "Rusty" <[email protected]> wrote in message
    > news:[email protected]...
    > > My excel sheet is like this
    > > Test Cases tab
    > > column A - Column B - Column C -
    > > Column D - Column E
    > > Name - Failed - No Run -
    > > Not Completed - <total>
    > > <2K3 TermServ 2k Blah 1> -1 - - -1
    > > <2K3 TermServ 2k Blah 2> - - 1 - 1
    > > <2K3 TermServ 2k Blah 3> - - 1 - 1
    > > <2K3 TermServ 2k Blah 4> 1 1
    > > AD2000 2k Blah 1 4 4
    > > AD2000 2k Blah 2 2 2
    > > AD2000 2k Blah 3 1 1
    > > AD2000 2k Blah 4 1 1
    > >
    > > What I want is a total (The last column - column E at the moment) number
    > > of
    > > tests for <2k3 TermServ*. The autosum that I had checks the first column
    > > for
    > > the name, and if it matches the criteria, it adds the numbers from column
    > > C.
    > > The problem is that my "TOTAL" column will move from column C to column E
    > > and
    > > to other columns as well depending on how the testing is progressing. I
    > > want
    > > it to be able to find the column with "Total" as the title and then add up
    > > those numbers (dependant on the name in Column A). I hope that explains
    > > it a
    > > bit better. BTW, Thanks Biff but that sum didn't work either. I got a
    > > N/A
    > > result.

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1