Sumproduct formula no longer working when changing pathways, or when recreating

[Jaybles]

Hi Guys - this is my first time using this site (seems awesome). Hopefully someone can give me some help with this.

Full disclosure:

I am by no means an expert with excel, but I do dabble and have taken a few courses in school that have focused on data analysis using excel. And I do a fair bit of excel work that can get a bit involved.

Situation:

A couple months ago I was conducting some trimester reporting and decided I wanted to create a spreadsheet that would summarize the values I needed to report on. This sheet would draw all its data from a larger Project Status sheet. I ran into a few problems trying to create formulas for some of the metrics I wanted numbers for, so I did some research online and found a formula that would allow me to count the number of unique entries based on a date range and which also had the same budget code. I ended up getting it work, however the data from this Project Status spreadsheet was later separated into about 4 other sheets. So, of course I figured this formula could be fixed easily by changing the cell pathways, however when everything was changed it kept reporting a value of 0 which isn’t true.

I admit I do not completely understand the formula, specifically the last part of it, so recreating it has been a bit of an issue. I made a mock example since the other is too complex and has sensitive information in it.

For this example, I am looking to find the number of unique addresses that were visited between 12/31/2019 and 5/1/2020 and that used the budget code 301-335.

Even when I do it here, I get 0 instead of what should be 2 (highlighted values). The formula I am using is:

=SUMPRODUCT(IF((B3:B15<=E4)*(B3:B15>=E3)*(C3:C15="301-335"), 1/COUNTIFS(B3:B15, "<="&E4, B3:B15, ">="&E3, A3:A15, A3:A15), 0))

The original formula I was trying to rework drawing from multiple sheets was as follows:

=SUMPRODUCT(IF(('Projects Approved'!$I:$I<=I3)*('Projects Approved'!$I:$I>=I2)*('Projects Approved'!$K:$K="301-335"), 1/COUNTIFS('Projects Approved'!$I:$I, "<="&I3, 'Projects Approved'!$I:$I, ">="&I2, 'Projects Approved'!$C:$C, 'Projects Approved'!$C:$C), 0))

Any help with this would be greatly appreciated, I have been almost pulling my hair out over it and it is likely something simple that I have overlooked or just don’t understand.

Thanks in advance

 

[KRice]

Welcome to the board! Give this a try and let me know if it gives the expected results:

Book1
	A	B	C	D	E	F	G
1					Search Criteria
2	Address	Date Visited	Budget Code		Date Range	Budget Code	Number Unique Addresses
3	123 Name Street	7/3/2000	301-314	From -->	12/31/2019	301-335	2
4	424 Jane Street	6/1/2009	301-335	To -->	5/1/2020
5	2333 Tom Street	10/3/2010	301-335
6	8989 Bird Street	11/9/2016	301-335
7	472 Time Street	7/29/2012	301-314
8	472 Time Street	9/14/2018	301-335
9	472 Time Street	1/15/2020	301-314
10	123 Name Street	3/3/2020	301-335
11	424 Jane Street	2/9/2019	301-335
12	424 Jane Street	2/12/2020	301-314
13	2333 Tom Street	6/23/2019	301-335
14	472 Time Street	4/17/2020	301-335
15	123 Name Street	7/28/2019	301-335
Jaybles

Cell Formulas
Range	Formula
G3	G3	=SUM(IF(($B$3:$B$15<=$E$4)*($B$3:$B$15>=$E$3)*($C$3:$C$15=$F$3), 1/COUNTIFS($B$3:$B$15,"<="&$E$4,$B$3:$B$15,">="&$E$3,$A$3:$A$15,$A$3:$A$15,$C$3:$C$15,$F$3)),0)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Since you're using Excel 365, you shouldn't have to confirm this formula as an array formula...Excel 365 will handle it natively.

You may not be familiar with the XL2BB add-in. It offers the ability to extract a small working example from your worksheet, including formulas, which can then be pasted into posts on this board. To copy the working example from this post into a blank worksheet, click on the clipboard icon (upper left icon at intersection of row and column labels). That will copy the example to your clipboard for convenient pasting into your workbook. Details of XL2BB can be found at the link in my signature block.

 

[Jaybles]

Thank you so much for the prompt reply and pointing me to XL2BB. I copied this into a blank excel workbook using the clipboard function and it generates a 0 answer for me? I saved your example to clipboard and posted it in a new workbook, I don't think I did anything that would affect that.

Book1
	A	B	C	D	E	F	G
1					Search Criteria
2	Address	Date Visited	Budget Code		Date Range	Budget Code	Number Unique Addresses
3	123 Name Street	7/3/2000	301-314	From -->	12/31/2019	301-335	0
4	424 Jane Street	6/1/2009	301-335	To -->	5/1/2020
5	2333 Tom Street	10/3/2010	301-335
6	8989 Bird Street	11/9/2016	301-335
7	472 Time Street	7/29/2012	301-314
8	472 Time Street	9/14/2018	301-335
9	472 Time Street	1/15/2020	301-314
10	123 Name Street	3/3/2020	301-335
11	424 Jane Street	2/9/2019	301-335
12	424 Jane Street	2/12/2020	301-314
13	2333 Tom Street	6/23/2019	301-335
14	472 Time Street	4/17/2020	301-335
15	123 Name Street	7/28/2019	301-335
Sheet1

Cell Formulas
Range	Formula
G3	G3	=SUM(IF(($B$3:$B$15<=$E$4)*($B$3:$B$15>=$E$3)*($C$3:$C$15=$F$3), 1/COUNTIFS($B$3:$B$15,"<="&$E$4,$B$3:$B$15,">="&$E$3,$A$3:$A$15,$A$3:$A$15,$C$3:$C$15,$F$3)),0)

 

[gaz_chops]

Did you enter as an array?

Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[KRice]

I think @gaz_chops is correct. Your profile shows that you're using 365, so I would expect the array formula to be treated natively, without any special entry method. But if you are not using this in Excel 365, the formula will need to be entered as an array formula. A quick tip...select the formula cell, hit F2 and then reenter with Ctrl-Shift-Enter.

 

[jamiemarie]

I think @gaz_chops is correct. Your profile shows that you're using 365, so I would expect the array formula to be treated natively, without any special entry method. But if you are not using this in Excel 365, the formula will need to be entered as an array formula. A quick tip...select the formula cell, hit F2 and then reenter with Ctrl-Shift-Enter.

Just chiming in on this, not trying to derail the thread. I'm also using Office 365 and have to Ctrl+Shift+Enter for the array formula. I also have not yet received the update that has the filter function, so maybe that has something to do with it?

 

[KRice]

Thanks for that explanation...that may help to clear up this issue.

 

[Jaybles]

Thanks a lot guys, entering the array formula did fix it. I do have 365 which is weird. I am going to try this with the original formula I was using to see if it works. I really appreciate you guys taking the time to help. I'll let you know if this fixes my original problem.

Thanks!