countif using multiple criteria
 

I tried researching the group, but there wasn't any response that
exactly matched what I had in mind, or at least that I could
interpret. I am trying to make a tally sort of similiar to the way
countif works, but I have 2 criteria instead of 1. For instance,
countif anything in column A from worksheetB matches the value in Cell
A2 on worksheetA -and- if anything in Column D of worksheetB matches
the value in cell D33 on worksheetA... so logically it would be
COUNTIF(worksheetB!A:A,A2 only if the same row also in columnK
worksheetB has the value stored in D33 on worksheetA)

visually it would look like:
wksA:
wksB:

A B C D
A B C D
Name Status'
johnd 0x183 held
johnd purged
johnd 0x184 held
janed review
johnd 0x185 review
sent
held
----
how would i be able to count if john's name shows up in column a on
wksB only if it also has "held" in column D of the same row john's
name appears?
would sumproduct be able to achieve this? Or does sumproduct only
work for numbers and dates?

-Marcus

countif using multiple criteria
 

Something along the lines of
=SUMPRODUCT(--(A2:A100="johd"),--(B2:B100="held"))
See these sites for details
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

Do a Google newsgroup search with: EXCEL SUMPRODUCT to get lots more
examples
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Marcusdmc" wrote in message
ups.com...
I tried researching the group, but there wasn't any response that
exactly matched what I had in mind, or at least that I could
interpret. I am trying to make a tally sort of similiar to the way
countif works, but I have 2 criteria instead of 1. For instance,
countif anything in column A from worksheetB matches the value in Cell
A2 on worksheetA -and- if anything in Column D of worksheetB matches
the value in cell D33 on worksheetA... so logically it would be
COUNTIF(worksheetB!A:A,A2 only if the same row also in columnK
worksheetB has the value stored in D33 on worksheetA)

visually it would look like:
wksA:
wksB:

A B C D
A B C D
Name Status'
johnd 0x183 held
johnd purged
johnd 0x184 held
janed review
johnd 0x185 review
sent
held
----
how would i be able to count if john's name shows up in column a on
wksB only if it also has "held" in column D of the same row john's
name appears?
would sumproduct be able to achieve this? Or does sumproduct only
work for numbers and dates?

-Marcus

countif using multiple criteria
 

Thank you! I didn't realize you have to declare your array more
detailed besides saying A:A or B:B... works great :)

{=SUM(IF(wks2!$A$1:$A$12000=B5,IF(wks2!$F$1:$F$120 00=$D$33,1,0),0))}


-Marcus