To count the data using multiple criteria in multiple columns

Hi,

I have datas in cell range A1:C4, where A being name of the projects (one 
project may occur in two or more places), B being the task number, C being 
week for completion of the task. Now I have to count the number of cells with 
given week of completion in a given project. This is to identify the number 
of tasks in a particular week in a particular project.

How shall I do this. Please help me with ideas.

0
Rajesh6582 (40)
12/12/2008 9:04:00 AM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
477 Views

Similar Articles

[PageSpeed] 50

=SUMPRODUCT(--(A1:A4="project"),--(C1:C4=week_num))

-- 
__________________________________
HTH

Bob

"Rajesh" <Rajesh@discussions.microsoft.com> wrote in message 
news:97EBA539-4631-4AF9-B53F-9744DAFEBFB9@microsoft.com...
> Hi,
>
> I have datas in cell range A1:C4, where A being name of the projects (one
> project may occur in two or more places), B being the task number, C being
> week for completion of the task. Now I have to count the number of cells 
> with
> given week of completion in a given project. This is to identify the 
> number
> of tasks in a particular week in a particular project.
>
> How shall I do this. Please help me with ideas.
> 


0
BobNGs (423)
12/12/2008 11:19:16 AM
Thanks philips for your reply. But I am not getting the desired result when I 
checked with an example. Whether sumproduct can be used to count the desired 
data from the array?

"Bob Phillips" wrote:

> =SUMPRODUCT(--(A1:A4="project"),--(C1:C4=week_num))
> 
> -- 
> __________________________________
> HTH
> 
> Bob
> 
> "Rajesh" <Rajesh@discussions.microsoft.com> wrote in message 
> news:97EBA539-4631-4AF9-B53F-9744DAFEBFB9@microsoft.com...
> > Hi,
> >
> > I have datas in cell range A1:C4, where A being name of the projects (one
> > project may occur in two or more places), B being the task number, C being
> > week for completion of the task. Now I have to count the number of cells 
> > with
> > given week of completion in a given project. This is to identify the 
> > number
> > of tasks in a particular week in a particular project.
> >
> > How shall I do this. Please help me with ideas.
> > 
> 
> 
> 
0
Rajesh6582 (40)
12/15/2008 2:16:04 PM
To be more clear on my problem:

ProjectA  Task1  Week1
ProjectB   Task1  week2
ProjectA  Task2  Week2
ProjectB  Task2  Week1
ProjectA  Task3 Week1

If these are my datas, then if my request is counting the number of datas 
with "ProjectA" and "Week1", the result should be 2.
 
"Rajesh" wrote:

> Thanks philips for your reply. But I am not getting the desired result when I 
> checked with an example. Whether sumproduct can be used to count the desired 
> data from the array?
> 
> "Bob Phillips" wrote:
> 
> > =SUMPRODUCT(--(A1:A4="project"),--(C1:C4=week_num))
> > 
> > -- 
> > __________________________________
> > HTH
> > 
> > Bob
> > 
> > "Rajesh" <Rajesh@discussions.microsoft.com> wrote in message 
> > news:97EBA539-4631-4AF9-B53F-9744DAFEBFB9@microsoft.com...
> > > Hi,
> > >
> > > I have datas in cell range A1:C4, where A being name of the projects (one
> > > project may occur in two or more places), B being the task number, C being
> > > week for completion of the task. Now I have to count the number of cells 
> > > with
> > > given week of completion in a given project. This is to identify the 
> > > number
> > > of tasks in a particular week in a particular project.
> > >
> > > How shall I do this. Please help me with ideas.
> > > 
> > 
> > 
> > 
0
Rajesh6582 (40)
12/15/2008 2:23:00 PM
As Bob said =SUMPRODUCT(--(A1:A4="projecta"),--(C1:C4="week1"))


-- 
Pecoflyer

Cheers - MS Excel Newbie
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=40100

0
12/15/2008 3:11:25 PM
Thank you all. It works.

"Pecoflyer" wrote:

> 
> As Bob said =SUMPRODUCT(--(A1:A4="projecta"),--(C1:C4="week1"))
> 
> 
> -- 
> Pecoflyer
> 
> Cheers - MS Excel Newbie
> ------------------------------------------------------------------------
> Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=40100
> 
> 
0
Rajesh6582 (40)
12/15/2008 4:07:01 PM
Reply:

Similar Artilces:

Using Remote Access within my own house??
I soon will have two XP computers, one in the basement and the main one on the second floor. Would it be possible to use the computer in the basement as the Remote Access user of the computer upstairs. This would avoid downloading files to the basment computer when I would really prefer them to be on the main compuer. It would mean that my outgoing email and news posts would all be in the same computer, the main one. I'm running DSL with a cable connection from the router to the main computer, and a wireless connect to the computer in the basement. I've never used...

Data Selection keyboard shortcuts -Source Data Window
When making scatterplots in Excel 2000, is there a keyboard shortcut to select the data once you are in the Source Data window? This is a very large spreadsheet and standard keyboard data selection shortcuts don't work when the Source Data window is open. When creating the chart, keyboard shortcuts (e.g. Ctrl+Home; Ctrl+End; Shift, followed by arrow key) should work while you're in the Source Data page of the Chart Wizard. However, after the chart has been created, if you choose Chart>Source Data, you'll have to select the cells with the mouse. Carol Hackney wrote: >...

how to use Excel 2007 Sensitivity Analysis
could any help provide a sample excel worksheet with sensitivity analysis demo, including use of scenario manager to generate scenario summary. ...

Question re. use of animation rebuild VBA
Hello, I am preparing a interactive ppt for use at a kiosk and would like to utilize the following VBA code into a macro in order to rebuild animation on previously viewed slides: Sub ResetSlide() ' The number after GotoSlide is the slide number. SlideShowWindows(1).View.GotoSlide 1, msoTrue End Sub (Dumb) Q#1: Does the part of the code that says " ' The number after GotoSlide is the slide number." stay in the code or is that just an instruction to the code user? Either way, does the (number) need to be changed for each situation? Q...

Sorting a column by using formula #3
I am trying to use sort function just to delete blank cells in between Sort order doesn't matter actually. Data is coming by the use of simple cell reference of "another sheet -- Prais ----------------------------------------------------------------------- Praise's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1558 View this thread: http://www.excelforum.com/showthread.php?threadid=27144 Hi you may use the following addin to filter out blank cells: http://www.rondebruin.nl/easyfilter.htm -- Regards Frank Kabel Frankfurt, Germany Praise wrote: &g...

data validation ?
I have two columns..I have written the below example to explain A B 1 a 2 b 3 c 4 d 5 e 6 f what I want to do is put a dropdown menu on a sheet, that when "1" is selected, in another cell, "a" would be displayed, and so on for all the way to "6"/"f" I have selected all of the numbers in the A column and name/defined it as "points". I have done the same with all in column B and names it "mm". I basically want someone to be able to select one of the points, and have it automatically display the mm value....any ...

use formula on external data range
Hi All, I've got a problem with data I read from a database. I wrote a VBA Sub in Excel 2007 with retrieves data via ActiveSheet.QueryTables.Add() per OLEDB out of a database. This works fine and puts the data in the range beginning on cell "A8" Now my problem: I have a formula '=SUM(A8:A10) in cell "B1". This formula always comes out = 0, although there are values in the cells. But if I edit cell "A8" without changing the value (just the alignment changes) it is recognized and the formula shows = 1 What am I missing here ? Thanks ...

Using Invoice template: overriding formulae
I am having a problem when using the Excel 2000 Invoice template. Sometimes I enter text into the 'Quantity' column. Then Excel is unable to calculate the Total, in the blue shaded area. My question is: How do I override the formula calculation in the shaded area to simply enter the numbers and/or text of my choosing? WTIA Andrew Stucken -- Evil flourishes when good men do nothing. Don'y know how to override the original protectiion. Easiest would be to copy and paste into new sheet and use this to create your own template. Roger "Enigmaman" wrote: > I a...

Erratic results from query criteria
I am getting different results from running the same query with the same selections. One moment it is all behaving as expected, the next it has gone haywire. (I have done what appears to me to be EXACTLY what I have done in another database, where it works perfectly every single time.) In a query I have, amongst others, the following fields: Category SubCategory Company I want to be able to select any OR ALL of the relevant fields. I have the following criteria: Like "*"&[Type Category otherwise leave blank for ALL]&"*" Like "*"&a...

All PIDS Used but need to re-install on new computer
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel We have a student edition with three installs already. However one of the Macs had to have a hard drive replaced due to failure and now we that we have re-installed we find that this PID is on conflict with the other ones. Apparently when I activated this re-install it used the same PID as one of the other installs. Our son (the student) has no idea wher his packaging. Is there a way to resolve this issue. it seems to me like Microsoft's activation is saying we activated too many installs? Rick Nash In article <5...

Unable to set up on-line account
Missage I get when I try to connect to an on-line bank. I checked my internet connection and it's working. I'm able to open IE browser within Money 2005. Please help. --------------------------- Microsoft Money --------------------------- Money cannot complete a required online call at this time. Please check your Internet connection settings and try again. --------------------------- OK Another reference to M05, what's going on? Bal wrote: > Missage I get when I try to connect to an on-line bank. > I checked my internet connection and it's working. I'...

Consolidate data with text
Excel Consolidate Data does not consolidate text. I need to somehow combine data from several different worksheets into one (the sheets contain text and numbers). The tables on each sheet are set up identically with identical column headers. Ideally, I would like to import worksheet #1, then beginning on the first blank row after worksheet #1, import (or copy and paste) worksheet #2, etc to form one combined database. Ideas, please? I should have mentioned these worksheets are in different workbooks. Ron de Bruin has tons of code samples for combining data here: http://www....

report in access (Count)
If i have data in CSV file and it show like belwo Column1 Column2 ABCD Saturday ABCD Saturday EFGH Sunday ABCD Saturday MMM Friday ABCD Monday EFGH Monday I want report show like Saturday Sunday Monday Tuesday Wednesday Thursday Friday ABCD 3 0 1 0 0 0 0 EFGH 0 1 1 0 0 ...

CRM Service Sheduling multiple service activities at once
We should be able to comnbine services in the service scheduling. For exemple, in truck fleet maintenance environment, often you will need to schedule an Oil Change and Change Break pads, or Oil Change and change tires.. The system should allow for scheduling both activites togheter, allowing for selecing whether the activites should be scheduled simultanneouslty or one after the other. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the mess...

sub tot column to next blank cell when blank cell exists in both c
I have posted this again to hope that someone will give me further assistance - I "ticked" the last post because it did work and well, but I realised what I actually need is slightly different. I asked for some coding that would search for each blank cell in a column and subtotal below it until the next blank cell. Stefi gave me this: Sub stotal() > Range("S1").End(xlDown).Activate > Range("S" & ActiveCell.Row - 1).Activate > Do While ActiveCell.Row <> Range("S" & Rows.Count).End(xlUp).Row + 1 > ...

are multiple Hyperlink destinations, in one cell possible
e.g.: =HYPERLINK(webaddress1),HYPERLINK(webaddress2,"2") Trying to get 2 web pages, one for the money,,, if that works, occasionally need to open range of cells web links, right now set up as hyperlinks 1,2; then 3,4 (2 for the show, or is it one to get ready jk) a b c d - 1 2 3 4 No. Sorry. What you CAN do is make it LOOK like one cell by putting borders around them, and perhaps turning off displaying gridlines. (It's one for the money, honey.) ************ Anne Troy www.OfficeArticles.com "nastech" <nastech@discussions.microsoft.com&g...

'Status' column from PurchaseOrderEntryDetail table
Hello, We're trying to populate the serial # into this table and run across the 'Status' column. What are the possible values for 'Status' column? Most of the time we see the value of '2', what does it mean? Please help. Many Thanks & Best Regard, Nikki ...

Filtering by certain criteria
Hello experts - I created a custom list where I have numbers of items listed there. Now I would need to set up view in which if user login to our sharepoint, he/she will see only items they are owners for. (note: items have been inserted into custom list by myself, not by owners) So I will basically filter column 'owner' by enteprise.ids that have been defined in another custom list titled as "Enterprise ID", in column "Title" to ensure sharepoint will recognize those IDs in column owner. How can I set the filter to ensure filter will work accordi...

Changing format of Data Validation's Input Message
I'm using Data Validations. The cell has an input message, which appears by default as a rectangular bubble right under the cell. Is it possible to change the format of this bubble, to different colours, fonts, borders, etc? I want to make it more noticeable. I didn't see any formatting capabilities in that dialog. Hall wrote: > > I'm using Data Validations. The cell has an input message, which appears by > default as a rectangular bubble right under the cell. > > Is it possible to change the format of this bubble, to different colours, > fonts, borde...

using option buttons #2
sorry im a bit of a noob, what do you mean by frame? :confused -- 4nd ----------------------------------------------------------------------- 4ndy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1494 View this thread: http://www.excelforum.com/showthread.php?threadid=26576 When you're designing the userform inside the VBE, you'll see an icon on the toolbox toolbar that looks like an outline of a frame with xyz in the top border. If you let your cursor linger over the icon, you'll see Frame as the tooltip. If you put your optionbuttons within that...

using office for different login users
Hi I'm new to Mac and would appreciate any help. I have installed office using 1 user a/c and when I login to another user a/c , I could not see the office programs. I found an earlier thread on this and the answer was to install office on the root application folder instead of the user application folder. reason being all programs installed in root application folder wil be available to all users. How do I check where I have installed it ? If i have installed it in the user application folder , do I have to uninstall ? When I install the office, I don't recall the s/w asking me whe...

Coercing Combo Box Value Based On Bound Column?
For instance: cboRatingType .RowSource="0;[NA];1;Fixed;2;Floating" .BoundColumn=0 .ColumnWidth=0;.25 Datawise, RatingTypeID_NA = 0 RatingTypeID_Fixed = 1 RatingTypeID_Floating = 2 Me.cboRatingType.Value=2 causes the box to show "2". But I want to force it to show "Floating" - but without having to stuff the literal value "Floating". i.e. I want to do it based on the ID value - for which I have a global constant defined. it shouldn't "PeteCresswell" <PeteCress@gmail.com> wrote in message news:1191608926.27...

Milo: Using Fp2003 to allow people to add an Outlook Calendar ite
Have looked around a lot on the net and can't find a clear answer to something I hope is simple. I'm the webmaster (FrontPage 2003) for a small non-profit. They asked if there were any way to put an "Add to my Outlook Calendar" link next to new events we post. I was able to create an .ics (iCalendar) file and add that to the site, but when I link to it, Firefox just opens it as text and IE just puts it in an obscure folder (C:\Documents and Settings\All Users\Documents\iMacros\Downloads) so neither actually added it to Outlook. I'm not loyal to doing t...

User Form Data Validation
How, Please could someone point me to a good tutorial on data validation for user forms?. I have a workbook that uses the INDIRECT method to restrict options dependant on the first selection. I want to create a user form for data entry, if I use the Excel data form from the menu bar, the data validation is not carried across. A url to advice would be great, Thanks, Mickey Are you using a combobox on that userform? Instead of using =indirect() in the userform, I just used code to point at that other range. I put two comboboxes and two commmand buttons (ok/cancel) on a userform. Th...

repeating a data pattern in excel
Im using excel to run a class register for a playgroup. Each pupil is booked in for a number of days per week. To show their forecast for the year, I'm manually entering the days they're going to be present each week - is there a way to do this automatically? For example, Ben is booked in for Mondays, Wednesday and Fridays - how can I copy this weekly pattern throughout the year without manually tabbing across and ticking the relevant days? Hi, Kel. To do this manually, but easily, type the first 3 "sets". Select them all and copy down using the fill handle. You coul...