Gathering and Counting Data Based On Criteria

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I have a woorbook with daily data entered into it's monthly worksheet. I
have a summary sheet as well that displays the year's information. I have to
find and count the number of days early a product was shipped for all my
accounts. I need this sorted for each account. For account number 1 in my
'April 2008' sheet (account 1 listed in cell C15) they had only one entry and
it was shipped on time (0 days early shown in cell G15 on the same sheet). I
need to find out how many jobs/products were shipped early for this account
for the year. Any help would be greatly appreciated.

Thank you,
Mike
 
I have a woorbook with daily data entered into it's monthly worksheet.
I have a summary sheet as well that displays the year's information.
I have to find and count the number of days early a product was
shipped for all my accounts. I need this sorted for each account.
For account number 1 in my 'April 2008' sheet (account 1 listed in
cell C15) they had only one entry and it was shipped on time (0 days
early shown in cell G15 on the same sheet). I need to find out how
many jobs/products were shipped early for this account for the year.
Any help would be greatly appreciated.

Thank you,
Mike
Click to expand...

you probably undferstood going into this that the summary page was going
to have a lot of formulas

on your Summary sheet cell A1 type in your Account 1

Then you are asking for 2 different things

1st
I have to find and count the number of days early a product was
shipped for all my accounts.
Click to expand...
this is a sum

=SUMIF('April 2008'!C:C,A1,'April 2008'!G:G)
or
=SUMIF('March 2008'!C:C,A1,'March 2008'!G:G)

2nd
I need to find out how
many jobs/products were shipped early for this account for the year.
Click to expand...

this is a count
=SUMPRODUCT(('April 2008'!C:C=A1)*('April 2008'!G:G>0))
or
=SUMPRODUCT(('March 2008'!C:C=A1)*('March 2008'!G:G>0))

these are for both March and April based on the account in A1
also, notice that i used the entire column...this will slow down your
spreadsheet. so instead of C:C and D:D you are going to want to give it
a range like C1:C100 and D1:D100

hope this helps.
 
Back
Top