Finding, Counting, and Summing Multiple Variables in a Data Table

**spb001** · 09-08-2010, 09:04 AM

I have attached a spreadsheet to my thread. I have several questions I need help on. Here is a summary on the information I would like to find.

I want to be able to find how many quotes I have received in a particular month and how many quotes I completed in a particular month. However, I need a breakdown of quotes that were completed during the month that were received during that month and other months.

Essentially, I would like an output that provided the following information:

INPUTS
Month: July
Year: 2010

OUTPUTS
Quotes:
Total monthly received quotes:
Quotes completed from May start date:
Quotes completed from June start date:
Quotes completed from July start date:

Sales
Total monthly sales:
Sales completed from May start date:
Sales completed from June start date:
Saless completed from July start date:

From that information I would like a breakdown of companies who bought from a quote and how many times they did.

I would eventually like to add graphs to give a visual representation as well.

Please view the following attachment for a better understanding. Any help is appreciated.

Thank you.

**ChemistB** · 09-08-2010, 09:16 AM

INPUTS
By INPUTs do you mean Column A date?

By OUTPUTS Quotes, you want the sum of each month and the total to date from May, June, ... ?
Quotes completed from July start date:

Same thing with Sales?

**spb001** · 09-08-2010, 09:43 AM

Originally Posted by ChemistB

INPUTS
By INPUTs do you mean Column A date?

By OUTPUTS Quotes, you want the sum of each month and the total to date from May, June, ... ?
Quotes completed from July start date:

Same thing with Sales?

No, by inputs you will type in the month and year you are interested in and then find the following outputs.

For instance I want to find all the received quotes for June 2010 without regard to the day. So, I'll go to column A and use column A as the reference to look up the input month and year values.

I used the following equation, but it doesn't work:

Please Login or Register  to view this content.

Then from the found range, I want to find the completed quotes using the Quoted Date column (Column E) and separate quotes that were started in previous months and the current month.

Is that possible?

**ChemistB** · 09-08-2010, 10:34 AM

Okay, you can use SUMPRODUCT instead of SUMIF (should have been COUNTIF anyway).

=SUMPRODUCT(--(MONTH($A$2:$A$12000)=7),--(YEAR($A$2:$A$12000)=2010))

The double unary (--) changes true/False to 1 or 0 and then sums them.

To add completed Quotes, add another argument

=SUMPRODUCT(--(MONTH($A$2:$A$12000)=7),--(YEAR($A$2:$A$12000)=2010),--(ISNUMBER($E$2:$E$12000))

Is that what you are looking for?

**spb001** · 09-08-2010, 11:20 AM

The formula came up with a number, but it was wrong. What if we applied the following. I wrote this out on paper so I apologize for the crude format.

COUNT the amount of quotes in the range E2:E12000, IF the start date is in May, AND the end date is in July

The answer is 4.

**ChemistB** · 09-08-2010, 11:32 AM

Okay, I need to understand better so we're on the same page. What my formula did.....
Formula 1, found out how many quotes were started in July (22) based on counting the number of July dates in Column A.
Formula 2, found out how many of those quotes were actually quoted (?) based on having a date in Column E (19 out of the original 22).
If you want to see how many started in May and ended in August, it would be this formula
=SUMPRODUCT(--(MONTH($A$2:$A$12000)=5),--(YEAR($A$2:$A$12000)=2010),--(MONTH($E$2:$E$12000)=8), --(YEAR($A$2:$A$12000)=2010))
In your example, there are 0 started in May

**spb001** · 09-08-2010, 11:39 AM

Oh, I apologize. Ok, yes, you are correct. Thank you very much!!

May I ask how I get trained to create formulas like this? Are there classes online or schools that have excel training?

**ChemistB** · 09-08-2010, 12:29 PM

I learned from reading the threads in this forum and then trying them out. I know there are a lot of 1 day (or more) seminars and webinars out there. Just need to google them.