SUMIFS or COUNTIFS help !

[Joe S]

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

 

[Bernard Liengme]

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

 

[Sheeloo]

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]

Thanks so much! It was a success!
--
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]

Thanks for your help. You folks are the best.