Home 
Search 
Today's Posts 
#1




logic that returns a count
I am trying to compare one column to another column and if the two conditions
I am looking at are true, I would like it to be counted. Eventually adding all occurances of those two conditions. EX A B C D F G 1 5/14/2006 Indiana/01 John Doe enroll mail enter 2 5/14/2006 Indiana/02 John Doe enroll mail enter 3 5/14/2006 Indiana/01 Jane Plur enroll mail enter 4 5/14/2006 Indiana/03 John Paul enroll mail enter 5 5/14/2006 Indiana/01 Joe Dirt salary fax I want the result to capture the "B" column if it says "Indiana/01" and then the "D" column if it says "enroll" and add all of the occuances of both of those and spit out a value. I would greatly appreciate any help I can get on the matter. thanks Danny 
#2




logic that returns a count
Enter a formula in column H:
=if(AND(b1="Indiana/01",d1="enroll"),1,0) Drag this formula down the spreadsheet and then at the bottom of that column use the formula: =sum(d1:d###) where ### is the row number of the last entry. HTH Ian "d_kight" wrote: I am trying to compare one column to another column and if the two conditions I am looking at are true, I would like it to be counted. Eventually adding all occurances of those two conditions. EX A B C D F G 1 5/14/2006 Indiana/01 John Doe enroll mail enter 2 5/14/2006 Indiana/02 John Doe enroll mail enter 3 5/14/2006 Indiana/01 Jane Plur enroll mail enter 4 5/14/2006 Indiana/03 John Paul enroll mail enter 5 5/14/2006 Indiana/01 Joe Dirt salary fax I want the result to capture the "B" column if it says "Indiana/01" and then the "D" column if it says "enroll" and add all of the occuances of both of those and spit out a value. I would greatly appreciate any help I can get on the matter. thanks Danny 
#3




logic that returns a count
Try this array* formula:
=SUM(IF((B1:B500="Indiana/01")*(D1500="enroll"),1,0)) * As this is an array formula, once you have typed it in (or subsequently edit it) you need to use CTRLSHIFTENTER instead of just ENTER. If you do this correctly, then Excel will wrap curly braces { } around the formula  you must not type these yourself. I have assumed the data is in rows 1 to 500  adjust to suit. Hope this helps. Pete 
#5




logic that returns a count
Thanks, Ian. I Tried this but it did not seem to work the way that I was
wanting it too. I will keep this and use it in the future for my inventory totals! "Ian P" wrote: Enter a formula in column H: =if(AND(b1="Indiana/01",d1="enroll"),1,0) Drag this formula down the spreadsheet and then at the bottom of that column use the formula: =sum(d1:d###) where ### is the row number of the last entry. HTH Ian "d_kight" wrote: I am trying to compare one column to another column and if the two conditions I am looking at are true, I would like it to be counted. Eventually adding all occurances of those two conditions. EX A B C D F G 1 5/14/2006 Indiana/01 John Doe enroll mail enter 2 5/14/2006 Indiana/02 John Doe enroll mail enter 3 5/14/2006 Indiana/01 Jane Plur enroll mail enter 4 5/14/2006 Indiana/03 John Paul enroll mail enter 5 5/14/2006 Indiana/01 Joe Dirt salary fax I want the result to capture the "B" column if it says "Indiana/01" and then the "D" column if it says "enroll" and add all of the occuances of both of those and spit out a value. I would greatly appreciate any help I can get on the matter. thanks Danny 
#6




logic that returns a count
Pete,
I think this is exactly what I was looking for. I tried something similar to this and it kept returning a #value? Is there a way to use this when there are blank cells as well? "Pete_UK" wrote: Try this array* formula: =SUM(IF((B1:B500="Indiana/01")*(D1500="enroll"),1,0)) * As this is an array formula, once you have typed it in (or subsequently edit it) you need to use CTRLSHIFTENTER instead of just ENTER. If you do this correctly, then Excel will wrap curly braces { } around the formula  you must not type these yourself. I have assumed the data is in rows 1 to 500  adjust to suit. Hope this helps. Pete 
#7




logic that returns a count
Ardus,
I really do enjoy the ease of the SumProduct fundtions! Now will this fumble if there are blank cells? "Ardus Petus" wrote: =SUMPRODUCT((B1:B999="Indiana/01")*(D1999="enroll") HTH  AP "d_kight" a Ã©crit dans le message de news: ... I am trying to compare one column to another column and if the two conditions I am looking at are true, I would like it to be counted. Eventually adding all occurances of those two conditions. EX A B C D F G 1 5/14/2006 Indiana/01 John Doe enroll mail enter 2 5/14/2006 Indiana/02 John Doe enroll mail enter 3 5/14/2006 Indiana/01 Jane Plur enroll mail enter 4 5/14/2006 Indiana/03 John Paul enroll mail enter 5 5/14/2006 Indiana/01 Joe Dirt salary fax I want the result to capture the "B" column if it says "Indiana/01" and then the "D" column if it says "enroll" and add all of the occuances of both of those and spit out a value. I would greatly appreciate any help I can get on the matter. thanks Danny 
#8




logic that returns a count
Hi, d_kight,
Yes it will (AFAIK)  AP "d_kight" a écrit dans le message de news: ... Ardus, I really do enjoy the ease of the SumProduct fundtions! Now will this fumble if there are blank cells? "Ardus Petus" wrote: =SUMPRODUCT((B1:B999="Indiana/01")*(D1999="enroll") HTH  AP "d_kight" a écrit dans le message de news: ... I am trying to compare one column to another column and if the two conditions I am looking at are true, I would like it to be counted. Eventually adding all occurances of those two conditions. EX A B C D F G 1 5/14/2006 Indiana/01 John Doe enroll mail enter 2 5/14/2006 Indiana/02 John Doe enroll mail enter 3 5/14/2006 Indiana/01 Jane Plur enroll mail enter 4 5/14/2006 Indiana/03 John Paul enroll mail enter 5 5/14/2006 Indiana/01 Joe Dirt salary fax I want the result to capture the "B" column if it says "Indiana/01" and then the "D" column if it says "enroll" and add all of the occuances of both of those and spit out a value. I would greatly appreciate any help I can get on the matter. thanks Danny 
#9




logic that returns a count
I think you will get #VALUE if you do not commit the formula with
CTRLSHIFTENTER or if you have errors in the columns. It should work with spaces in either B or D because these will return FALSE, and so 0 will be added to the cumulative sum  why not try it? Pete 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Count consecutive dates only  Excel Discussion (Misc queries)  
Count number of uniques starting with a given letter?  Excel Discussion (Misc queries)  
Match Last Occurrence of two numbers and Count to Previous Occurence  Excel Worksheet Functions  
Logic statement returns wrong answer.  Excel Worksheet Functions  
GET.CELL  Excel Worksheet Functions 