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

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" wrote in message
...
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

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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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

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.

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" wrote in message
...
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.

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" wrote in message
...
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.