Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I count if column A="active" and column E="Job"?
Basically have a spreadsheet of info on current jobs. On sheet 2 i would
like a count of active jobs. On Sheet 1 data is laid out as follows: Column A = "a" or blank Column E = "J" or "S" or blank The problem is that old jobs are also in the sheet. So countif(E:E,"J") returns all the j's even if the job is no longer active (Column A is blank, but column E has a "J"). Formula needed is: If Col A="a" and column E="J" then count. Basically want to find number of Js when column A ="a". I can use Countif for number of "a'"s but can't figure out how to condition a formula to count the "j" in only the rows with a corresponding "a" in column A. Thank you for your help! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I count if column A="active" and column E="Job"?
=SUMPRODUCT((A1:A51="a")*(E1:E51="J"))
Regards Trevor "Brandoni" wrote in message ... Basically have a spreadsheet of info on current jobs. On sheet 2 i would like a count of active jobs. On Sheet 1 data is laid out as follows: Column A = "a" or blank Column E = "J" or "S" or blank The problem is that old jobs are also in the sheet. So countif(E:E,"J") returns all the j's even if the job is no longer active (Column A is blank, but column E has a "J"). Formula needed is: If Col A="a" and column E="J" then count. Basically want to find number of Js when column A ="a". I can use Countif for number of "a'"s but can't figure out how to condition a formula to count the "j" in only the rows with a corresponding "a" in column A. Thank you for your help! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I count if column A="active" and column E="Job"?
=SUMPRODUCT(((A1:A100="a")+(A1:A100="")),((B1:B100 ="J")+(B1:B100="S")+(B1:B100=""))
You cannot use whole column (as in A:A) with SUMPRODUCT best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Brandoni" wrote in message ... Basically have a spreadsheet of info on current jobs. On sheet 2 i would like a count of active jobs. On Sheet 1 data is laid out as follows: Column A = "a" or blank Column E = "J" or "S" or blank The problem is that old jobs are also in the sheet. So countif(E:E,"J") returns all the j's even if the job is no longer active (Column A is blank, but column E has a "J"). Formula needed is: If Col A="a" and column E="J" then count. Basically want to find number of Js when column A ="a". I can use Countif for number of "a'"s but can't figure out how to condition a formula to count the "j" in only the rows with a corresponding "a" in column A. Thank you for your help! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I count if column A="active" and column E="Job"?
Yes, I would use SUMPRODUCT and my formula is the same as Trevor's except for the ranges.
I am curious if we don't use SUMPRODUCT, how would we do it, say with COUNT, IF, AND etc.? The only alternative I can think of is to use COUNTIF on a helper column (e.g. H) containing this formula =AND(A1="a",E1="j") (copied down the column). Then use the following formula: =COUNTIF(H1:H10,TRUE) TIA Epinn "Trevor Shuttleworth" wrote in message ... =SUMPRODUCT((A1:A51="a")*(E1:E51="J")) Regards Trevor "Brandoni" wrote in message ... Basically have a spreadsheet of info on current jobs. On sheet 2 i would like a count of active jobs. On Sheet 1 data is laid out as follows: Column A = "a" or blank Column E = "J" or "S" or blank The problem is that old jobs are also in the sheet. So countif(E:E,"J") returns all the j's even if the job is no longer active (Column A is blank, but column E has a "J"). Formula needed is: If Col A="a" and column E="J" then count. Basically want to find number of Js when column A ="a". I can use Countif for number of "a'"s but can't figure out how to condition a formula to count the "j" in only the rows with a corresponding "a" in column A. Thank you for your help! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I count if column A="active" and column E="Job"?
<<<"I am curious if we don't use SUMPRODUCT, how would we do it, say with
COUNT, IF, AND etc.?" How about an *array* formula using Sum()? Try this: =SUM((A1:A100="a")*(E1:E100="J")) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... Yes, I would use SUMPRODUCT and my formula is the same as Trevor's except for the ranges. I am curious if we don't use SUMPRODUCT, how would we do it, say with COUNT, IF, AND etc.? The only alternative I can think of is to use COUNTIF on a helper column (e.g. H) containing this formula =AND(A1="a",E1="j") (copied down the column). Then use the following formula: =COUNTIF(H1:H10,TRUE) TIA Epinn "Trevor Shuttleworth" wrote in message ... =SUMPRODUCT((A1:A51="a")*(E1:E51="J")) Regards Trevor "Brandoni" wrote in message ... Basically have a spreadsheet of info on current jobs. On sheet 2 i would like a count of active jobs. On Sheet 1 data is laid out as follows: Column A = "a" or blank Column E = "J" or "S" or blank The problem is that old jobs are also in the sheet. So countif(E:E,"J") returns all the j's even if the job is no longer active (Column A is blank, but column E has a "J"). Formula needed is: If Col A="a" and column E="J" then count. Basically want to find number of Js when column A ="a". I can use Countif for number of "a'"s but can't figure out how to condition a formula to count the "j" in only the rows with a corresponding "a" in column A. Thank you for your help! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I count if column A="active" and column E="Job"?
No need a helper column. There are many way to count without using SUMPRODUCT
Function. =SUM((A1:A100="a")*(B1:B100="J")) or =SUM(IF((A1:A100="a")*(B1:B100="J"),1)) or =SUM(IF(A1:A100="a",IF(B1:B100="J",1))) All of these above are array formula you have to commit Ctrl Shift enter (not just enter) "Epinn" wrote: Yes, I would use SUMPRODUCT and my formula is the same as Trevor's except for the ranges. I am curious if we don't use SUMPRODUCT, how would we do it, say with COUNT, IF, AND etc.? The only alternative I can think of is to use COUNTIF on a helper column (e.g. H) containing this formula =AND(A1="a",E1="j") (copied down the column). Then use the following formula: =COUNTIF(H1:H10,TRUE) TIA Epinn "Trevor Shuttleworth" wrote in message ... =SUMPRODUCT((A1:A51="a")*(E1:E51="J")) Regards Trevor "Brandoni" wrote in message ... Basically have a spreadsheet of info on current jobs. On sheet 2 i would like a count of active jobs. On Sheet 1 data is laid out as follows: Column A = "a" or blank Column E = "J" or "S" or blank The problem is that old jobs are also in the sheet. So countif(E:E,"J") returns all the j's even if the job is no longer active (Column A is blank, but column E has a "J"). Formula needed is: If Col A="a" and column E="J" then count. Basically want to find number of Js when column A ="a". I can use Countif for number of "a'"s but can't figure out how to condition a formula to count the "j" in only the rows with a corresponding "a" in column A. Thank you for your help! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I count if column A="active" and column E="Job"?
Thank you for your suggestions.
I am very much aware that SUM plus CSE and SUM(IF( )) plus CSE are equivalent to SUMPRODUCT. However, in this example, we want to count; so I was "fixated" on COUNT and COUNT(IF( )) and I got stuck. I have another question and I am going to start my own thread "SUM, COUNT and SUMPRODUCT?" Brandoni, hope you won't mind my question. Sometimes, I find it easier for all readers to group all formulae together. Have you found your favourite formula out of the choices provided? I wonder if anyone else has another suggestion. We'll wait and see. Epinn "Teethless mama" wrote in message ... No need a helper column. There are many way to count without using SUMPRODUCT Function. =SUM((A1:A100="a")*(B1:B100="J")) or =SUM(IF((A1:A100="a")*(B1:B100="J"),1)) or =SUM(IF(A1:A100="a",IF(B1:B100="J",1))) All of these above are array formula you have to commit Ctrl Shift enter (not just enter) "Epinn" wrote: Yes, I would use SUMPRODUCT and my formula is the same as Trevor's except for the ranges. I am curious if we don't use SUMPRODUCT, how would we do it, say with COUNT, IF, AND etc.? The only alternative I can think of is to use COUNTIF on a helper column (e.g. H) containing this formula =AND(A1="a",E1="j") (copied down the column). Then use the following formula: =COUNTIF(H1:H10,TRUE) TIA Epinn "Trevor Shuttleworth" wrote in message ... =SUMPRODUCT((A1:A51="a")*(E1:E51="J")) Regards Trevor "Brandoni" wrote in message ... Basically have a spreadsheet of info on current jobs. On sheet 2 i would like a count of active jobs. On Sheet 1 data is laid out as follows: Column A = "a" or blank Column E = "J" or "S" or blank The problem is that old jobs are also in the sheet. So countif(E:E,"J") returns all the j's even if the job is no longer active (Column A is blank, but column E has a "J"). Formula needed is: If Col A="a" and column E="J" then count. Basically want to find number of Js when column A ="a". I can use Countif for number of "a'"s but can't figure out how to condition a formula to count the "j" in only the rows with a corresponding "a" in column A. Thank you for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I count if column A="active" and column E="Job" in a list? | Excel Worksheet Functions | |||
Column reference and column count | Excel Discussion (Misc queries) | |||
parsing text in a multiple column count | Excel Worksheet Functions | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions |