Countif forumulas that refer to more than one column
Hello,
Im trying to setup a worksheet which my department can use to track how many jobs of each different kind it has on the go. At the top of the worksheet, I want to build a section showing the number of jobs outstanding by type (so, for instance, 2 writing jobs, 3 proofing jobs and so on). To do this, I have to get the cells in question to look at two columns. The first column is a validated list of job types (copywriting, editing, subbing, proofing). The second column shows the status of the job (awaiting commission, in progress, submitted, approved). What I want to do is write a formula that says the following: Look a column b, if the value is €śawaiting commission€ť or €śin progress€ť, check column a. If the value in column a is (for instance) €śproofing€ť, count that cell. Ive had a few goes, without success, can anyone help? All and any help much appreciated. Thanks Karl |
Countif forumulas that refer to more than one column
You need to use SUMPRODUCT, not COUNTIF to count based on multiple criteria.
A formula like =SUMPRODUCT(--(A1:A10="awaiting commission"),--(B1:B10="proofing")), which will count all records for which column A has "awaiting commission" and column B has "proofing". Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Karl" wrote: Hello, Im trying to setup a worksheet which my department can use to track how many jobs of each different kind it has on the go. At the top of the worksheet, I want to build a section showing the number of jobs outstanding by type (so, for instance, 2 writing jobs, 3 proofing jobs and so on). To do this, I have to get the cells in question to look at two columns. The first column is a validated list of job types (copywriting, editing, subbing, proofing). The second column shows the status of the job (awaiting commission, in progress, submitted, approved). What I want to do is write a formula that says the following: Look a column b, if the value is €śawaiting commission€ť or €śin progress€ť, check column a. If the value in column a is (for instance) €śproofing€ť, count that cell. Ive had a few goes, without success, can anyone help? All and any help much appreciated. Thanks Karl |
Countif forumulas that refer to more than one column
SUMPRODUCT is the function you need he
=SUMPRODUCT((A1:A5="writing")*(B1:B5="awaiting")) Change the address ranges and text to match the pairings you want to count. You can even use cell addresses in place of the "writing" and "awaiting" portions of the formula, so you could have a pair of cells to choose them from to have a single place to show counts based on what you have in them. "Karl" wrote: Hello, Im trying to setup a worksheet which my department can use to track how many jobs of each different kind it has on the go. At the top of the worksheet, I want to build a section showing the number of jobs outstanding by type (so, for instance, 2 writing jobs, 3 proofing jobs and so on). To do this, I have to get the cells in question to look at two columns. The first column is a validated list of job types (copywriting, editing, subbing, proofing). The second column shows the status of the job (awaiting commission, in progress, submitted, approved). What I want to do is write a formula that says the following: Look a column b, if the value is €śawaiting commission€ť or €śin progress€ť, check column a. If the value in column a is (for instance) €śproofing€ť, count that cell. Ive had a few goes, without success, can anyone help? All and any help much appreciated. Thanks Karl |
Countif forumulas that refer to more than one column
Hi Karl
Sir Jlatham, hello again... Without mentioning how the "status and data" on both column has to be encoded either by a List or manual typing... I would prefer to suggest a Pivot Table...for tracking of counted records... Yet, I still do not know where is the official forum group in excel to source for good help on Pivot table....i hope someone can clarify this to us... regards, -- ***** birds of the same feather flock together.. "JLatham" wrote: SUMPRODUCT is the function you need he =SUMPRODUCT((A1:A5="writing")*(B1:B5="awaiting")) Change the address ranges and text to match the pairings you want to count. You can even use cell addresses in place of the "writing" and "awaiting" portions of the formula, so you could have a pair of cells to choose them from to have a single place to show counts based on what you have in them. "Karl" wrote: Hello, Im trying to setup a worksheet which my department can use to track how many jobs of each different kind it has on the go. At the top of the worksheet, I want to build a section showing the number of jobs outstanding by type (so, for instance, 2 writing jobs, 3 proofing jobs and so on). To do this, I have to get the cells in question to look at two columns. The first column is a validated list of job types (copywriting, editing, subbing, proofing). The second column shows the status of the job (awaiting commission, in progress, submitted, approved). What I want to do is write a formula that says the following: Look a column b, if the value is €śawaiting commission€ť or €śin progress€ť, check column a. If the value in column a is (for instance) €śproofing€ť, count that cell. Ive had a few goes, without success, can anyone help? All and any help much appreciated. Thanks Karl |
Countif forumulas that refer to more than one column
I'm not sure which one would be best to go to with specific Pivot Table
questions - I suspect this one is close to being it. But I work so little with Pivot Tables myself that when I realize I need one, I have to drag out the books to get it done. The other side of that coin is that by not using the feature often, it is easily overlooked by me as a potential solution. "driller" wrote: Hi Karl Sir Jlatham, hello again... Without mentioning how the "status and data" on both column has to be encoded either by a List or manual typing... I would prefer to suggest a Pivot Table...for tracking of counted records... Yet, I still do not know where is the official forum group in excel to source for good help on Pivot table....i hope someone can clarify this to us... regards, -- ***** birds of the same feather flock together.. "JLatham" wrote: SUMPRODUCT is the function you need he =SUMPRODUCT((A1:A5="writing")*(B1:B5="awaiting")) Change the address ranges and text to match the pairings you want to count. You can even use cell addresses in place of the "writing" and "awaiting" portions of the formula, so you could have a pair of cells to choose them from to have a single place to show counts based on what you have in them. "Karl" wrote: Hello, Im trying to setup a worksheet which my department can use to track how many jobs of each different kind it has on the go. At the top of the worksheet, I want to build a section showing the number of jobs outstanding by type (so, for instance, 2 writing jobs, 3 proofing jobs and so on). To do this, I have to get the cells in question to look at two columns. The first column is a validated list of job types (copywriting, editing, subbing, proofing). The second column shows the status of the job (awaiting commission, in progress, submitted, approved). What I want to do is write a formula that says the following: Look a column b, if the value is €śawaiting commission€ť or €śin progress€ť, check column a. If the value in column a is (for instance) €śproofing€ť, count that cell. Ive had a few goes, without success, can anyone help? All and any help much appreciated. Thanks Karl |
All times are GMT +1. The time now is 06:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com