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

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

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


.

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


.