| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula gone array
I have a situation in which there are 4 columns that need
to meet the criteria in order for the correct numbers to appear. There are 8000 rows of info. I got the formula to work to count the number of items (33) fitting those 4 criterias. But, now I need the dollars of the 33 items. I am using the following but it is summing all 8000 records. The following is also what gave me the correct number of items (33) from a different column (G). Can anyone see my disconnect in the formula? =SUM(Detail!$O$2:$O$10000,(IF(Detail!$K$2:$K$10000 =1998,IF (Detail!$M$2:$M$10000="LB",IF(Detail!$J$2:$J$10000 ="Bodily Injury",IF(Detail!$G$2:$G$10000="Closed",1,0)))))) Thanks in advance to anyone who responds. *** John |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formula gone array
assume the dollars are in column L
=SUM(Detail!$O$2:$O$10000,(IF(Detail!$K$2:$K$10000 =1998,IF (Detail!$M$2:$M$10000="LB",IF(Detail!$J$2:$J$10000 ="Bodily Injury",IF(Detail!$G$2:$G$10000="Closed",Detail!$L $2:$L$10000)))))) -- Regards, Tom Ogilvy "JohnE" wrote in message ... I have a situation in which there are 4 columns that need to meet the criteria in order for the correct numbers to appear. There are 8000 rows of info. I got the formula to work to count the number of items (33) fitting those 4 criterias. But, now I need the dollars of the 33 items. I am using the following but it is summing all 8000 records. The following is also what gave me the correct number of items (33) from a different column (G). Can anyone see my disconnect in the formula? =SUM(Detail!$O$2:$O$10000,(IF(Detail!$K$2:$K$10000 =1998,IF (Detail!$M$2:$M$10000="LB",IF(Detail!$J$2:$J$10000 ="Bodily Injury",IF(Detail!$G$2:$G$10000="Closed",1,0)))))) Thanks in advance to anyone who responds. *** John |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formula gone array
Tom, my apologies. The dollars are in O. So I removed
the first "Detail" after the sum and made the one you added at the end into O and it worked. Thanks. *** John -----Original Message----- assume the dollars are in column L =SUM(Detail!$O$2:$O$10000,(IF(Detail!$K$2:$K$1000 0=1998,IF (Detail!$M$2:$M$10000="LB",IF(Detail!$J$2:$J$1000 0="Bodily Injury",IF(Detail!$G$2:$G$10000="Closed",Detail ! $L$2:$L$10000)))))) -- Regards, Tom Ogilvy "JohnE" wrote in message ... I have a situation in which there are 4 columns that need to meet the criteria in order for the correct numbers to appear. There are 8000 rows of info. I got the formula to work to count the number of items (33) fitting those 4 criterias. But, now I need the dollars of the 33 items. I am using the following but it is summing all 8000 records. The following is also what gave me the correct number of items (33) from a different column (G). Can anyone see my disconnect in the formula? =SUM(Detail!$O$2:$O$10000,(IF(Detail! $K$2:$K$10000=1998,IF (Detail!$M$2:$M$10000="LB",IF(Detail! $J$2:$J$10000="Bodily Injury",IF(Detail!$G$2:$G$10000="Closed",1,0)))))) Thanks in advance to anyone who responds. *** John . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formula gone array
didn't even notice it there - you said that it worked to get a count, so I
assumed it worked as written, but gave a count instead of a sum. -- Regards, Tom Ogilvy "JohnE" wrote in message ... Tom, my apologies. The dollars are in O. So I removed the first "Detail" after the sum and made the one you added at the end into O and it worked. Thanks. *** John -----Original Message----- assume the dollars are in column L =SUM(Detail!$O$2:$O$10000,(IF(Detail!$K$2:$K$1000 0=1998,IF (Detail!$M$2:$M$10000="LB",IF(Detail!$J$2:$J$1000 0="Bodily Injury",IF(Detail!$G$2:$G$10000="Closed",Detail ! $L$2:$L$10000)))))) -- Regards, Tom Ogilvy "JohnE" wrote in message ... I have a situation in which there are 4 columns that need to meet the criteria in order for the correct numbers to appear. There are 8000 rows of info. I got the formula to work to count the number of items (33) fitting those 4 criterias. But, now I need the dollars of the 33 items. I am using the following but it is summing all 8000 records. The following is also what gave me the correct number of items (33) from a different column (G). Can anyone see my disconnect in the formula? =SUM(Detail!$O$2:$O$10000,(IF(Detail! $K$2:$K$10000=1998,IF (Detail!$M$2:$M$10000="LB",IF(Detail! $J$2:$J$10000="Bodily Injury",IF(Detail!$G$2:$G$10000="Closed",1,0)))))) Thanks in advance to anyone who responds. *** John . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array formula SUMIF with 2D sum_range array | Excel Worksheet Functions | |||
| Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
| Find specific value in array of array formula | Excel Worksheet Functions | |||
| meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
| Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions | |||
Linear Mode
