COUNTIF HELP

[instereo911 via OfficeKB.com]

I am having major trouble with a countif formula

My Sheet looks like this

A B C D
1 Call AM 136
2 Call AM 50
3 Net AM 38
4 Call CU 150
5 Net CU 32
6 Net CU 12
7 Call EF 145

I want another cell to do the following: COUNTIF B1:B600 = "AM", COUNT THE
AMOUNT OF CELLS THAT ARE OVER 30.

also figured to throw this in the mix too - IF A1:A600 = Call and B1:B600 is
"Am", Count THE AMOUNT OF CELLS THAT ARE OVER 30.


The problem with this is that I have two worksheets full of this data and i
have about 80 "UNITS (i.e. AM, CU)" that I am working with... so manually
counting them is kind of out of the question.


Thanks again everyone

 

[Dave Peterson]

=sumproduct(--(b1:b600="AM"),--(c1:c600>30))
and
=sumproduct(--(a1:a600="Call"),--(b1:b600="AM"),--(c1:c600>30))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

==========
You may want to add headers (in row 1) and add a formula in column D.

Formula in D2:
=if(c2>30,1,0)
and drag down

Then you could use that table to create a pivottable.

Select the table (a1601)
data|pivottable
follow the wizard until you get to a step with a Layout button on it.
Click that button.

Drag the header for Column A to the Row field
Drag the header for column B to the column field
drag the header for column D to the Data field
If the header for column D doesn't show "Sum of" Cancel this and go back to the
original table and make sure your formula is correct in D2601.

Then finish up this pivottable.

 

[instereo911 via OfficeKB.com]

Dave-

Thank you very very much!!! This worked like a charm!!

=sumproduct(--(b1:b600="AM"),--(c1:c600>30))
and
=sumproduct(--(a1:a600="Call"),--(b1:b600="AM"),--(c1:c600>30))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

==========
You may want to add headers (in row 1) and add a formula in column D.

Formula in D2:
=if(c2>30,1,0)
and drag down

Then you could use that table to create a pivottable.

Select the table (a1601)
data|pivottable
follow the wizard until you get to a step with a Layout button on it.
Click that button.

Drag the header for Column A to the Row field
Drag the header for column B to the column field
drag the header for column D to the Data field
If the header for column D doesn't show "Sum of" Cancel this and go back to the
original table and make sure your formula is correct in D2601.

Then finish up this pivottable.

I am having major trouble with a countif formula

[quoted text clipped - 24 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200705/1