Sum of one column based on Month value in another?

[MajesticNJ]

Good morning all,

I have a situation where we have a sales spreadsheet and I need to provide sales values for any given month.

In this case, the date is in one column and includes month, day, year. Date format is MM/DD/YYYY. Column is E.

Then there is another column that contains a dollar value. Column is V.

I need to provide the sum of all dollar values in any given month. Being able to do it by a range of months would also be a helpful plus.

Lastly, the sum would output on a different tab. The data collection tab is named ORDERS.

Hope this is enough information to assist. Thanks in advance!

Fred

 

[jasonb75]

The only thing I can think of is your H5 formula is looking for Jan Feb Mar etc.

The date values are in the ORDERS tab and in column E. The format is mm/dd/yyyy. I need to show the sum of all orders for the month by month.

Let us break down the H2 formula, the first range refers to the column of dates in mm/dd/yyyy format (although the format is not critical as long as they are valid dates).

=SUMPRODUCT(--(TEXT($C$2:$C$6,"Mmm")=G2),$D$2:$D$6)

The middle range refers to the cell next to the results table that holds the word "Jan", i.e. the row heading / title.

=SUMPRODUCT(--(TEXT($C$2:$C$6,"Mmm")=G2),$D$2:$D$6)

The last range refers to the column of dollar values.

=SUMPRODUCT(--(TEXT($C$2:$C$6,"Mmm")=G2),$D$2:$D$6)

Does that help?

 

[MajesticNJ]

Thanks so much guys. I think I have it working now.

So I was asked for a follow up.

Now on top of this I need to be able to show the number of completed orders for a given month.

We DO have a column that shows when an order is completed and in that column we put a date. So rather than come up with a value, I guess I would want to just count the number of dates that are filled in in the column within a specific month.

Column E contains the date that an order was started.
Column N contains the date that an order was completed.
I need to count the number of orders that completed in a month period.
The TAB the data is on remains to be ORDERS.

Thanks for the help everyone!!!