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

Jaybles

New Member
Joined
Jul 17, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.

excelexample.png


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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows
Welcome to the board! Give this a try and let me know if it gives the expected results:
Book1
ABCDEFG
1Search Criteria
2AddressDate VisitedBudget CodeDate RangeBudget CodeNumber Unique Addresses
3123 Name Street7/3/2000301-314From -->12/31/2019301-3352
4424 Jane Street6/1/2009301-335To -->5/1/2020
52333 Tom Street10/3/2010301-335
68989 Bird Street11/9/2016301-335
7472 Time Street7/29/2012301-314
8472 Time Street9/14/2018301-335
9472 Time Street1/15/2020301-314
10123 Name Street3/3/2020301-335
11424 Jane Street2/9/2019301-335
12424 Jane Street2/12/2020301-314
132333 Tom Street6/23/2019301-335
14472 Time Street4/17/2020301-335
15123 Name Street7/28/2019301-335
Jaybles
Cell Formulas
RangeFormula
G3G3=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

New Member
Joined
Jul 17, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFG
1Search Criteria
2AddressDate VisitedBudget CodeDate RangeBudget CodeNumber Unique Addresses
3123 Name Street7/3/2000301-314From -->12/31/2019301-3350
4424 Jane Street6/1/2009301-335To -->5/1/2020
52333 Tom Street10/3/2010301-335
68989 Bird Street11/9/2016301-335
7472 Time Street7/29/2012301-314
8472 Time Street9/14/2018301-335
9472 Time Street1/15/2020301-314
10123 Name Street3/3/2020301-335
11424 Jane Street2/9/2019301-335
12424 Jane Street2/12/2020301-314
132333 Tom Street6/23/2019301-335
14472 Time Street4/17/2020301-335
15123 Name Street7/28/2019301-335
Sheet1
Cell Formulas
RangeFormula
G3G3=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

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
Did you enter as an array?

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

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Jun 24, 2020
Messages
67
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows
Thanks for that explanation...that may help to clear up this issue.
 

Jaybles

New Member
Joined
Jul 17, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,134
Messages
5,570,364
Members
412,321
Latest member
Yusuf_A
Top