Get the count and sum based on a conditions 1

[stubnski]

Hi everyone, this is what I have been banging my head against.

I have three columns - column A are dates ex.
1/3/06
1/3/2006
1/6/2006
1/10/2006 ....
formated as date
The dates are random.

In column B I have text ex.
IN HOUSE
CNTRY
PHH...
formated as general

In column C I have currency ex.
$1234.21
$4635.96
$73646.09...
formated as currency

Now what I have been trying to figure out is how to get the count of records with dates between the begining of the week and end of the week that also contain the text "IN HOUSE". After that in a seperate cell I just want the sum of the currency for those records. Any prod in the right direction would be very helpful.
TIA

Stubnski

 

[stubnski]

Forgot to mention Excel 2000
Thanks again

 

[AnotherHiggins]

->with dates between the begining of the week and end of the week

What week? Last week? What day of the week do you consider a week to start on?

If you want to dynamically determine 'last week' - that is, the week before whenever you are looking at the sheet - and you want the first day of the week to be Monday, then you can use something like:
[COLOR=blue white]=int((now()-7)/7)*7+2[/color]
and the most recent Sunday would be
[COLOR=blue white]=int((now())/7)*7+1[/color]

Armed with that knowledge, you can use SUMPRODUCT to count how many of the dates are during last week.

[COLOR=blue white]=SUMPRODUCT(--(A2:A10>=INT((NOW()-7)/7)*7+2)*--(A2:A10<INT((NOW())/7)*7+2))[/color]

Now just add an argument to find out how many times "IN HOUSE" appears during last week. That formula would look like this:
[COLOR=blue white]=sumproduct(--(A2:A10>=int((now()-7)/7)*7+2)*--(A2:A10<int((now())/7)*7+2)*--(B2:B10="IN HOUSE"))[/color]

To add the corolating dollars, just add C2:C10 to the criteria:
[COLOR=blue white]=SUMPRODUCT(--(A2:A10>=INT((NOW()-7)/7)*7+2)*--(A2:A10<INT((NOW())/7)*7+2)*--(B2:B10="IN HOUSE")[!]*--(C2:C10)[/!])[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

Oh, yeah. All criteria in a SUMPRODUCT formula have to be the same length. That is, You [!]can't[/!] have
=SUMPRODUCT(--(A2:A10)*--(B2:B11))

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[stubnski]

Hi thanks for the reply. The start of the week is monday. It's based on a monthly schedule ie. 4 weeks, last week carries over to the 1st week of the next month. The dates are manually put in for now.

 

[AnotherHiggins]

OK, same idea but replace the "INT((NOW()-7)/7)*7+2" with a cell reference, eg
[COLOR=blue white]=sumproduct(--(A2:A10>=$F$1)*--(A2:A10<$F$2)*--(B2:B10="IN HOUSE"))[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[stubnski]

This is exactly what I need, thanks a ton anotherhiggins!

 

[AnotherHiggins]

[CHEERS]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.