I have an Excel07 table with several columns, some with text, some with
numbers. I need to find a function to help me show a sum total (on another
worksheet in the same workbook)from a column of data from a couple of
different conditions.

For example the first column of data shows a list of states, a few columns
over the data shows "FT" or "PT". The next column shows the number of shifts
that are covered.

I want to show the total number FT shifts for a particular state in one cell
of my worksheet and the total number of PT shifts for that same state in the
adjacent cell.

I have tried this with sumifs and countifs but keep getting error messages.

Can someone please help.?
--
joe s

In A1 of Sheet2 I have a state abbreviation such as NY
In B1 this formula will sum column C entries which have that state in column
A, and FT in column B
=SUMIFS(Sheet1!C:C,Sheet1!A:A,Sheet2!A1,Sheet1!B:B ,"FT")
Note that SUMIFS (with a final S) is new to Excel 2007
Prior to that you would have used
=SUMPRODUCT(--(Sheet1!A:A=Sheet1!A1),--(Sheet1!B:B="FT"),Sheet1!C:C)
except only Excel 2007 allows full column references in SUMPRODUCT
best wishes


"Joe S" wrote in message
...
I have an Excel07 table with several columns, some with text, some with
numbers. I need to find a function to help me show a sum total (on another
worksheet in the same workbook)from a column of data from a couple of
different conditions.

For example the first column of data shows a list of states, a few columns
over the data shows "FT" or "PT". The next column shows the number of
shifts
that are covered.

I want to show the total number FT shifts for a particular state in one
cell
of my worksheet and the total number of PT shifts for that same state in
the
adjacent cell.

I have tried this with sumifs and countifs but keep getting error
messages.

Can someone please help.?
--
joe s

Try
=SUMPRODUCT(--(Sheet1!A1:A100="CA"),--(Sheet1!B1:B100="FT"),(Sheet1!C1:C100))

Replace Sheet1 with your sheet name... add '' around the name if it has
spaces.
Change 100 to the last row..

Change A to the letter of State Column, B to the FT/PT column, and C to the
number of shifts column... You can put it any where other than the ranges
covered in the formula...

You can also use cells in place of "CA" and "PT" if those cells contain
valid values...
For example if Sheet2 A1 containts CA and B1 contains PT then you can use
this in any cell of Sheet2 (other than A1 or B1 :-)

=SUMPRODUCT(--(Sheet1!A1:A100=A1),--(Sheet1!B1:B100=B1),(Sheet1!C1:C100))

"Joe S" wrote:

I have an Excel07 table with several columns, some with text, some with
numbers. I need to find a function to help me show a sum total (on another
worksheet in the same workbook)from a column of data from a couple of
different conditions.

For example the first column of data shows a list of states, a few columns
over the data shows "FT" or "PT". The next column shows the number of shifts
that are covered.

I want to show the total number FT shifts for a particular state in one cell
of my worksheet and the total number of PT shifts for that same state in the
adjacent cell.

I have tried this with sumifs and countifs but keep getting error messages.

Can someone please help.?
--
joe s

You're probably have to post an example of your worksheet.

However....be sure that in your sumif or countif formula, that your
formula is something like: countif(A1:A50, "=50"). Need to make sure
the qualifier is within double quotes "".


--
Christopher770
------------------------------------------------------------------------
Christopher770's Profile: http://www.thecodecage.com/forumz/member.php?userid=188
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=77869

Thanks so much! It was a success!
--
joe s


"Bernard Liengme" wrote:

In A1 of Sheet2 I have a state abbreviation such as NY
In B1 this formula will sum column C entries which have that state in column
A, and FT in column B
=SUMIFS(Sheet1!C:C,Sheet1!A:A,Sheet2!A1,Sheet1!B:B ,"FT")
Note that SUMIFS (with a final S) is new to Excel 2007
Prior to that you would have used
=SUMPRODUCT(--(Sheet1!A:A=Sheet1!A1),--(Sheet1!B:B="FT"),Sheet1!C:C)
except only Excel 2007 allows full column references in SUMPRODUCT
best wishes


"Joe S" wrote in message
...
I have an Excel07 table with several columns, some with text, some with
numbers. I need to find a function to help me show a sum total (on another
worksheet in the same workbook)from a column of data from a couple of
different conditions.

For example the first column of data shows a list of states, a few columns
over the data shows "FT" or "PT". The next column shows the number of
shifts
that are covered.

I want to show the total number FT shifts for a particular state in one
cell
of my worksheet and the total number of PT shifts for that same state in
the
adjacent cell.

I have tried this with sumifs and countifs but keep getting error
messages.

Can someone please help.?
--
joe s

Thanks for your help. You folks are the best.
--
joe s


"Sheeloo" wrote:

Try
=SUMPRODUCT(--(Sheet1!A1:A100="CA"),--(Sheet1!B1:B100="FT"),(Sheet1!C1:C100))

Replace Sheet1 with your sheet name... add '' around the name if it has
spaces.
Change 100 to the last row..

Change A to the letter of State Column, B to the FT/PT column, and C to the
number of shifts column... You can put it any where other than the ranges
covered in the formula...

You can also use cells in place of "CA" and "PT" if those cells contain
valid values...
For example if Sheet2 A1 containts CA and B1 contains PT then you can use
this in any cell of Sheet2 (other than A1 or B1 :-)

=SUMPRODUCT(--(Sheet1!A1:A100=A1),--(Sheet1!B1:B100=B1),(Sheet1!C1:C100))

"Joe S" wrote:

I have an Excel07 table with several columns, some with text, some with
numbers. I need to find a function to help me show a sum total (on another
worksheet in the same workbook)from a column of data from a couple of
different conditions.

For example the first column of data shows a list of states, a few columns
over the data shows "FT" or "PT". The next column shows the number of shifts
that are covered.

I want to show the total number FT shifts for a particular state in one cell
of my worksheet and the total number of PT shifts for that same state in the
adjacent cell.

I have tried this with sumifs and countifs but keep getting error messages.

Can someone please help.?
--
joe s