Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 24th 07, 06:47 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2007
Posts: 26
Default 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


  #2   Report Post  
Old September 24th 07, 06:53 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,393
Default 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



  #3   Report Post  
Old September 24th 07, 08:17 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2007
Posts: 26
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Countif with multiple criteria Allen Excel Discussion (Misc queries) 0 January 19th 10 06:51 PM
Nesting COUNTIF for multiple criteria in multiple columns NeedExcelHelp07 Excel Worksheet Functions 1 December 12th 07 05:47 PM
Countif Multiple Criteria hot dogs Excel Discussion (Misc queries) 5 October 26th 06 12:00 PM
Using Countif with multiple criteria JustLearning Excel Worksheet Functions 1 August 3rd 06 09:30 PM
Countif with multiple criteria and multiple worksheets JJ Excel Worksheet Functions 1 December 28th 04 06:37 PM


All times are GMT +1. The time now is 12:13 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017