SUMPRODUCT with date range and conditions

Hi
I have a CRM spreadsheet where I am trying to create an historical calendar record of all productivity and sales per calendar month.

This is where I have got to:
=SUMPRODUCT(--('Communication Log'!$A$6:$A57<=B44),--('Communication Log'!$A$6:$A57>=A44),--('Communication Log'!$I17:$I57=1))
but i get #VALUE

Also tried
=SUMPRODUCT(('Communication Log'!$A$6:$A57>=DATE(7,1,2019))*('Communication Log'!$A$6:$A57<=DATE(7,31,2019))*('Communication Log'!$I17:$I57=3))
but I get #N/A

I am working on the overview tab. Column A is the start of the date range (1/7/2019) and column B is the end (31/7/2019)
Data is held on the Communication Log sheet with the dates of entry in column A: and the criteria (type of productivity) in Column I eg -1=call, 2= email, 3 = meeting etc.

The question i am trying to answer is how much productivity, per type, was carried out between 1/7/19 and 31/7/2019.

Having looked on line I have tried various options eg: with " " either side of <= and replacing -- with * but errors appear.

Can anyone guide me on this?
Ruth

Hi, thanks for your reply.

That made sense, tried, but it didn't work

However I have tried this =SUMPRODUCT(--('Communication Log'!$A$6:$A46>=$A$44),--('Communication Log'!$A$6:$A46<=$B$44),--('Communication Log'!$I6:$I46=1)) and its delivering the answers

I have attached the spreadsheet I am working on.

The tab is the Dashboard tab and I am trying to populate F44, G44 and H44 by bringing in the sum of the criteria from the Opportunity Tracker.

Thanks to anyone who can help

Hi
I need to see a sum of all the entries which have been 'Won'. So sum of Opportunity Tracker/T within the date range set on Dashboard A:B where Opportunity Tracker/W = 9 and 10

Hope that explains a bit clearer

Sorry Ali.

At the moment it would be £18765

I'm glad its not just me! Yes SUMIFS works - thank you Ali, much appreciated.

One last question - if I want to read status code 10 as well as the 9, how would I add that on?
If it helps, another option is to sum Opportunity Tracker/Q as this segregates codes 9 and 10 from the others. I have only just thought of this but hoping it may make things easier?

Thanks Ali,

Came up with an error so I have amended to read ">="&9 and the same for 10 and the answer remains the same, which is right. Yey!

I am pretty new to these formulas and not sure what purpose the & does or why we have to use " " either side of <=. Can you enlighten me?

Ah, right, Thanks CK76. SUMPRODUCT does seem to be more convoluted although I'm sure it has benefits on other tasks.