If then statements invloving dates

[Randomhero180]

Hey all,

I need to run a report based on dates withing the spreadsheet. Along with a conditional

For example if the date is between 9/1/11 and 9/30/11 in column H and for every instance of "Disc sent" in columns B through G I want it to count 1.

This is my first spreadsheet with a lot of coding and complex functions, I really appreciate the help!

RH

 

[T. Valko]

Hey all,

I need to run a report based on dates withing the spreadsheet. Along with a conditional

For example if the date is between 9/1/11 and 9/30/11 in column H and for every instance of "Disc sent" in columns B through G I want it to count 1.

This is my first spreadsheet with a lot of coding and complex functions, I really appreciate the help!

RH

Try this...

Use cells to hold the criteria:

• J2 = 9/1/2011
• K2 = Disc sent

Then:

=SUMPRODUCT((TEXT(H2:H20,"mmmyyyy")=TEXT(J2,"mmmyyyy"))*(B2:G20=K2))

 

[Randomhero180]

works perfect! thanks you so much.

 

[T. Valko]

works perfect! thanks you so much.

You're welcome. Thanks for the feedback!

 

[Randomhero180]

Dont mean to test you skills again... but I'm thinking about adding in another criteria to count.

This is what I modified your code to

=SUMPRODUCT((TEXT(Sheet1!H19:H12678,"mmmyyyy")=TEXT(G1,"mmmyyyy"))*(Sheet1!B19:G12678=A2))

This counts everything within the range that is in the month starting 9/1/2011 and that contains the value Disc Sent now how would I add another criteria?

For example each instance of Disc Sent belongs to a certain group, (A,B,C,D) and i want to count how many instances of Disc sent belong to each group, along with the total...

Thanks again!

 

[T. Valko]

Dont mean to test you skills again... but I'm thinking about adding in another criteria to count.

This is what I modified your code to

=SUMPRODUCT((TEXT(Sheet1!H19:H12678,"mmmyyyy")=TEXT(G1,"mmmyyyy"))*(Sheet1!B19:G12678=A2))

This counts everything within the range that is in the month starting 9/1/2011 and that contains the value Disc Sent now how would I add another criteria?

For example each instance of Disc Sent belongs to a certain group, (A,B,C,D) and i want to count how many instances of Disc sent belong to each group, along with the total...

Thanks again!

Let's assume column A contains the group designation.

H1 = some group designation like A.

=SUMPRODUCT((Sheet1!A19:A12678=H1)*(TEXT(Sheet1!H19:H12678,"mmmyyyy")=TEXT(G1,"mmmyyyy"))*(Sheet1!B19:G12678=A2))

 

[Randomhero180]

Figured it out! You answered before I could post my solution, but I got it!

Thanks again!

 

[T. Valko]

Figured it out! You answered before I could post my solution, but I got it!

Thanks again!

Good deal! Thanks for the feedback!