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

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

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

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
>

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

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.

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.

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