CountIf 3 conditions met, and between 2 dates

[vinchamp2]

I am have been trying to get a certain formula to work, for sometime, and can't find any answers, so hopefully someone can help!

I am trying to sum the dollars of a product when they are sold, between each month, so i have several columns

date column, product column, sold (y/n), and price column

So what i am trying to do, is to get the formula to add up all the prices, when the sold column says yes, and when a certain product is listed, and when between 2 dates.

Is this possible in Excel? If so How? Please Help

 

[mvptomlinson]

Generally you can use a SUMPRODUCT function for this, or in Excel 2007+ you can use SUMIFS. If you can't figure either out, provide additional detail as to your columns and data (or better yet, see my signature for file upload sites).

 

[vinchamp2]

I am using Microsoft 2007.

Column B. Is my DATE column

Column F. Is my Product Column

Column I is my Sold (YES/NO) if a product is sold, then YES is written in the column

Column E. is my PRICE column

I am trying to sum, prices, when a Product is listed, and Yes, And between 2 dates, 01-01-2010 and 01-31-2010. I have the dates listed on separate sheet in the workbook, so i can just click on two cells, that i am searching for.

Any help i would appreciate, thanks for your feedback

 

[hiker95]

vinchamp2,

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[mvptomlinson]

Perhaps something like:

=SUMPRODUCT(--(F1:F100="some_product"),--(B1:B100>=DATE(2010,1,1)),--(B1:B100<=DATE(2010,1,31)),--(I1:I100="YES"),E1:E100)

 

[vinchamp2]

No luck, with that formula, i was reading this article, and i was able to add, how many times that a particular product, was entered between certain dates, here is the formula i used

=SUMPRODUCT(('Reps Name'!F35:G40=List!A4)*('Reps Name'!B35:B40>=List!G5)*('Reps Name'!B35:B40<=List!H5))

I listed all the ranges of dates, on a separate sheet. But my only problem, is i can get to SUM!! the $$$ When 2 conditions are met. The product, and the Yes.

Any Suggestions?

 

[vinchamp2]

Got It, Thanks For your Help!!!

Here is the formula that did it!!

=SUMPRODUCT(IF(('Reps Name'!F35:G40=List!A4)*('Reps Name'!I35:I40=List!E4)*('Reps Name'!B35:B40>=List!G5)*('Reps Name'!B35:B40<=List!H5),'Reps Name'!E35:E40))