sumif formula for date and month with match criteria

Hi

1) =SUMPRODUCT((MONTH(A2:A32)=F4)*(B2:B32))

2) =SUMPRODUCT((A2:A32>=F12)*(A2:A32<=G12)*(B2:B32))

thanks guys, that is the range for row i have to put right? and both using sumproduct

how about i want to use range for A:A and B:B that meet the item i want to search, if u can see that is item A1 and A2
so if i select item A1 for selected month, it will sum for item A1 for that month

or for this sumif with dynamic range (a:a) is not suitable to use?

note A1 is not column A1, it is item, see i put A1 and A2 for column under item

thanks

abu

See this workbook, select from the drop-down in F4
Note the formula in C2 down

@ rscsmith Just 1 minute!

@ alimamak

I am not able to follow you. What is this item A1?

In SUMPRODUCT, YOU HAVE TO USE SPECIAL RANGE, LIKE a1:a32. So you can use something like this.

=SUMPRODUCT((MONTH(A2:A150000)=F4)*(B2:B150000))

=SUMPRODUCT((A2:A150000>=F12)*(A2:A150000<=G12)*(B2:B150000))

Still don't understand..

You formulae, inclunding Items in Column A, work OK! So where is the problem?

=SUMPRODUCT((A2:A32=F3)*(MONTH(B2:B32)=G4)*(C2:C32))

=SUMPRODUCT((A2:A32=F11)*(B2:B32>=G12)*(B2:B32<=H12)*(C2:C32))

Try this workbook

I only have 2003 and SUMPRODUCT() doesn't work for a whole column, it does for 2007 and above, but it is not a good idea to refer to whole columns when using SUMPRODUCT().

Okay, perhaps it is now time to introduce you to Dynamic Named Ranges.
Check out the Names Manager to see how this works.

!/. You can now add more data to your table and the formula and drop-downs will adjust to suit.

2/. If you add a new item to Column A this will be detected in the Column "Unique Items" and the drop-downs will adjust to suit.
Keep your Items as text strings, if you use a numeric value this will fail and require a slightly different formula.
Don't leave blanks in Column A

3/. Try selecting a Date in the "Date To" cells that is before the "Date From" cells.
Check out the Conditional Formatting to see how this works.

4/. Now that we are using the exact range sizes with the names SUMPRODUCT() should be every bit as efficient as SUMIFS(), but I don't have access to 2007 at present so I can't check this out.

Perhaps Cutter will help you out there?

Originally Posted by Marcol

Okay, perhaps it is now time to introduce you to Dynamic Named Ranges.
Check out the Names Manager to see how this works.

!/. You can now add more data to your table and the formula and drop-downs will adjust to suit.

2/. If you add a new item to Column A this will be detected in the Column "Unique Items" and the drop-downs will adjust to suit.
Keep your Items as text strings, if you use a numeric value this will fail and require a slightly different formula.
Don't leave blanks in Column A

3/. Try selecting a Date in the "Date To" cells that is before the "Date From" cells.
Check out the Conditional Formatting to see how this works.

4/. Now that we are using the exact range sizes with the names SUMPRODUCT() should be every bit as efficient as SUMIFS(), but I don't have access to 2007 at present so I can't check this out.

Perhaps Cutter will help you out there?

thanks marcol, i get it what u try to say.. for item it be wise to make it unique 1st. and for date from and to, it be wise to make name range so it will be easy to manage and to add at data validation

i think cutter busy with other matter, i will try to play with ur example

actually i like more sumif rather than sumproduct as the syntac for sumif look short than sumproduct as for vlookup replacement. and for date i still new on using sumif and sumproduct and i see for sumif can use whole column range that would be usefull and do not need to make modification to it column range

thanks again marcol