Hi Folks,
I've attached a spreadsheet. In Sheet 2 I want to populate the table in green. So for each date and each criteria on top I want excel to scan Sheet 1 and for Jan-12 return how many of each criteria (from column C) were contained etc. I know how to do this task for numbers with the SUMIF function which allows a sum range section but COUNTIF only allows range and criteria and not count range.
Can anyone advise?
Thank you!
Last edited by controlfreak; 07-16-2013 at 07:23 AM.
you don't need a count range-each row that meets the criteria is counted as 1
=COUNTIFS(Sheet1!$F:$F,$J3,Sheet1!$C:$C,K$2)
Josie
if at first you don't succeed try doing it the way your wife told you to
In K3 and copy down and across.
=SUMPRODUCT((MONTH(Sheet1!$F$2:$F$100)=MONTH($J3))*(Sheet1!$C$2:$C$100=K$2))
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
In K3
=COUNTIFS(Sheet1!$F:$F,$J3,Sheet1!$C:$C,K$2)
copy down and across
Life's a spreadsheet, Excel!
Say thanks, Click *
Thanks guys!!
I knew I could 'count' on ye!
(Sorry for the bad excel pun!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks