count entries that equal one criteria if another column meets anot

[ccKennedy]

hi,
i'd like to use the countif feature but only if another column meets another
criteria.
Example:
A B
Product Jan
Personnel Feb

so, i want to know how many times "Product" occurred in Jan, how many times
"Personnel" occurred in Feb, etc.
help?

 

[Bernard Liengme]

=SUMPRODUCT(--(A1:A100="Product"),--(B1:B100="Jan"))
I am assuming Jan is text not a formatted date

You can use cell references
=SUMPRODUCT(--(A1:A100=K2),--(B1:B100=L1))
where K1 holds "Product" and L1 hold "Jan" (without quotes, of course)

If this is a big, serious project, this may be the time to learn about pivot
tables
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2005/06/23/download-pivottable-parameters/

best wishes
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

 

[Pecoflyer]

hi,
i'd like to use the countif feature but only if another column meet
another
criteria.
Example:
A B
Product Jan
Personnel Feb

so, i want to know how many times "Product" occurred in Jan, how man
times
"Personnel" occurred in Feb, etc.
help?

Hi,

have you tried a Pivot Table

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste

 

[ccKennedy]

Thank you! what do the dashes do? i also tried without the dashes and that
worked, i just had to make "product" a cell reference and "Jan" the text with
quotes.
have tried Pivot tables, but no luck - i do have blank cells in teh
worksheet, so it won't read properly...
too bad.

thanks so much for your reply!

 

[ccKennedy]

i have, but my workbook has blank cells & it's my understanding that the
pivot table won't support blank cells...

 

[Pecoflyer]

i have, but my workbook has blank cells & it's my understanding tha
the
pivot table won't support blank cells...



column meets anot - The Code Cage Forums
(http://www.thecodecage.com/forumz/showthread.php?t=54296)

Pivot table supports blank cells, but they will not allow you t
group.
As for the -- it is called an unary operator and coerces True/Fals
outcomes to 1/0 thus allowing the SUMPRODUCT fi-unction to wor

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste

 

[ccKennedy]

thank you
i'm using the sumproduct function, but it's now taking almost a minute to
calculate after i enter the data in the cell - says "calculating" and the
status percentage at the bottom - is this normal?
perhaps i should revisit pivot tables...

 

[Bernard Liengme]

For a full explanation visit the site of J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes

 

[Shane Devenshire]

Hi,

Pivot Tables are designed to group your data in ways you can't even do in
the spreadsheet! So I'm not sure what you mean by this "Pivot table
supports blank cells, but they will not allow you to group."

 

[Shane Devenshire]

Hi,

Most assuredly, you should revisit the pivot table option:
1. Select your data with one row of titles.
2. Choose Data, Pivot Table and Pivot Chart Report, Finish
3. Drag the Month field into the row area,
4. Drag the Category field into the row area also (I am assuming the name of
the column with Personal, Product and so on, is Category)
5. Drag the Category field into the Data area.