Listing info from Annual, to a more detailed worksheet by Month
 

Hi - have the following problem-
Need to pull information from an annual quoting spread sheet. Here are the
columns I need to pull the INFO FROM in one spreadsheet:

Date quote rcv'd
Producer the quote assigned to
Level of account (a,b,c,d),
Status of quote- not worked, pending, complete
date completed and then (# of days it took to complete)

I know I can do the following to get the Level of accounts - just not sure
about how to incorporate the rest of these things:
=SUMPRODUCT(--(QUOTELOG!H3:H13={"a","b","c"}))

And this for finding the Producer code
=COUNT(IF((QUOTELOG!$B$3:$B$2553=39995)*(QUOTELOG !$B$3:$B$2553<40026)*(QUOTELOG!$D$3:$D$2553="CP7") *(QUOTELOG!$I$3:$I$2553="Completed"),0))

just not sure about how to incorporate the rest of these things:

Thanks for any help. cindy

Listing info from Annual, to a more detailed worksheet by Month
 

Maybe...

=SUMPRODUCT((QuoteLog!$H$3:$H$2553={"a","b","c"})
*(QuoteLog!$B$3:$B$2553=39995)
*(QuoteLog!$B$3:$B$2553<40026)
*(QuoteLog!$D$3:$D$2553="CP7")
*(QuoteLog!$I$3:$I$2553="Completed"))

or since those values in column B are dates:

=SUMPRODUCT((QuoteLog!$H$3:$H$2553={"a","b","c"})
*(QuoteLog!$B$3:$B$2553=date(2009,7,1))
*(QuoteLog!$B$3:$B$2553<date(2008,8,1))
*(QuoteLog!$D$3:$D$2553="CP7")
*(QuoteLog!$I$3:$I$2553="Completed"))

Or even:

=SUMPRODUCT((QuoteLog!$H$3:$H$2553={"a","b","c"})
*(TEXT(QuoteLog!$B$3:$B$2553,"yyyymm")="200907")
*(QuoteLog!$D$3:$D$2553="CP7")
*(QuoteLog!$I$3:$I$2553="Completed"))

Cindyt wrote:

Hi - have the following problem-
Need to pull information from an annual quoting spread sheet. Here are the
columns I need to pull the INFO FROM in one spreadsheet:

Date quote rcv'd
Producer the quote assigned to
Level of account (a,b,c,d),
Status of quote- not worked, pending, complete
date completed and then (# of days it took to complete)

I know I can do the following to get the Level of accounts - just not sure
about how to incorporate the rest of these things:
=SUMPRODUCT(--(QUOTELOG!H3:H13={"a","b","c"}))

And this for finding the Producer code
=COUNT(IF((QUOTELOG!$B$3:$B$2553=39995)*(QUOTELOG !$B$3:$B$2553<40026)*(QUOTELOG!$D$3:$D$2553="CP7") *(QUOTELOG!$I$3:$I$2553="Completed"),0))

just not sure about how to incorporate the rest of these things:

Thanks for any help. cindy

--

Dave Peterson