SUMPRODUCT Formula for Inventory of Properties in specific states & date ranges.

[cpt4500]

Afternoon Excel Members,

We have a yearly inventory tracker that was created by a past employee. The inventory track keeps record of the properties we get in certain states in certain date ranges.

These are the formulas previously used in the 2014 tracker:
=SUMPRODUCT(--(Table3[State]="co"),--(Table3[Date FSM Assigned]>=$B5),--(Table3[Date FSM Assigned]<$B6))
=SUMPRODUCT(--(Table3[State]="nm"),--(Table3[Date FSM Assigned]>=$B5),--(Table3[Date FSM Assigned]<$B6))
=SUMPRODUCT(--(Table3[State]="tx"),--(Table3[Date FSM Assigned]>=$B5),--(Table3[Date FSM Assigned]<$B6))
=SUMPRODUCT(--(Table3[State]="ut"),--(Table3[Date FSM Assigned]>=$B5),--(Table3[Date FSM Assigned]<$B6))
=SUMPRODUCT(--(Table3[State]="mi"),--(Table3[Date FSM Assigned]>=$B5),--(Table3[Date FSM Assigned]<$B6))

Our new 2015 tracker is on a completely new excel workbook. I tried carrying over the previous years formulas to read from table1 over table3 but I get a bunch of errors.

For the 2015 tracker, all of the new property data is on a 2nd sheet within table1.

My goal is to have the SUMPRODUCT formula count how many properties we have acquired in each specific state within each month's time frame. The state is located in column E of table1, the date acquired is in column S of table1.

Appreciate your advice and help in advance.

Tracker Image.jpg
Data Image.jpg

[skywriter]

I don't think the pictures are very helpful. It would be better if you posted a workbook with actual sample data(sensitive items removed). Be sure the data accurately reflects your actual workbook. Manually enter the desired results and explain how you want those results calculated, so we can see if our solution is working and the format of the results is what you expect.

View Pic

[cpt4500]

Thank you. Attached is the file. I had to place sample data into it due to the nature of our contract.

I want the SUMPRODUCT formula to sum up each property by state and month it was acquired in.

State is in the Acq sheet under column E and the date acquired is in Acq sheet under column S.

[newdoverman]

Welcome to the forum.

The Table names are likely to blame. On the original, the table name is Table3 on your new workbook the table name is Table1. There may be other errors to fix as well but that is a place to start. Rename Table1 to Table3 and see if the errors are fixed.

If not, please upload your workbook.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[newdoverman]

There are extraneous characters in the TABLE headers. The formulae are fine the way they are but if you click in the headers of the table, you will see that the headers have a return that shouldn't be there that causes a new line. Delete that character and enter a space unless the character is at the end of the header.

I have corrected this on the sample.

[cpt4500]

Thank you. That works!

If you can, can you post a screen shot of what you mean by "There are extraneous characters in the TABLE headers. The formulae are fine the way they are but if you click in the headers of the table, you will see that the headers have a return that shouldn't be there that causes a new line"

I am having a hard time finding what you are referring to on the original file I provided above. I want to understand the error so I can correct it in the future.

Thank you again!

[newdoverman]

Here is a picture showing what I mean using the first header. You see there is two lines in the formula bar when there should only be one line. There is probably a line break (Alt + Enter) that you can't actually see but the result is there. Delete the characters between and enter a space. If the character is the last character in the formula bar just delete that character without entering a space as before.Capture.PNG