# Solved Formula to split from Total \$ & Value to \$ & Value by State

February 21, 2018 at 19:37:41
Specs: Windows 7
 I am trying to collect data from a worksheet named Full Data. I need the following information.I need to sort this data from Worksheet Full Data by “State” which is determined in Column E then by “Type of Project” in Column J and then by “Type of Product” in Columns L – Z.I need to determine the qty and the value.For example: my worksheet which is collating the data from worksheet "Full Data" looks a bit like this: State A Column A Column B Column C Column D East End West end South End North end1. Aged Care Qty Value Qty Value Qty Value Qty Value2. Aquatic centre Qty Value Qty Value Qty Value Qty Value 3. Community Qty Value Qty Value Qty Value Qty Value State B Column Row East End West end South End North end1. Aged Care Qty Value Qty Value Qty Value Qty Value2. Aquatic centre Qty Value Qty Value Qty Value Qty Value 3. Community Qty Value Qty Value Qty Value Qty ValueI currently am using this formula to collate Value of all states as one total but I need to separate by individual States:By Value{=SUM(IF('Excel - Full Data'!\$J\$4:\$J\$2100="Aquatic Care",IF(MOD(COLUMN('Excel - Full Data'!\$L\$1:\$Z\$1),2)=0,IF(NOT(ISNA(MATCH('Excel - Full Data'!\$L\$4:\$Z\$2100,{"East End"},0))),'Excel - Full Data'!\$M\$4:\$AA\$2100))))}And all States by Qty: =SUMPRODUCT(('Excel - Full Data'!\$J\$4:\$J\$2100="Community")*(MOD(COLUMN('Excel - Full Data'!\$L\$1:\$Z\$1),2)=0)*(NOT(ISNA(MATCH('Excel - Full Data'!\$L\$4:\$Z\$2100,{"East End"},0))))*('Excel - Full Data'!\$M\$4:\$AA\$2100<>""))Any help would be appreciated.

See More: Formula to split from Total \$ & Value to \$ & Value by State

February 27, 2018 at 17:09:28
 If you are expecting us to reconstruct your input data based solely on your formulas, I'd pretty sure your expectations are way too high. ;-)Keep in mind that we can't see your workbook from where we're sitting. If we have no idea what your input data looks likes, there no way we can figure out how to get you the output you want.Your first goal should be to make it as easy as possible to help you. The more energy we spend trying to figure out what we are working with, the less energy we have to to work on a solution. If you give us some input data that we can Copy/Paste into a workbook, then we can get directly to work on a solution.

#1
February 22, 2018 at 06:29:48
 First, a posting tip: Please click on the How-To link at the end of this post and read the instructions on how to format example data so that it is easier for us to read. Then edit/repost your data so that the columns line up correctly. Don't forget to use Column letters and Row numbers as shown in the example. Thanks!

Report •

#2
February 22, 2018 at 15:00:54
 I am trying to collect data from a worksheet named Full Data. I need the following information.I need to sort this data from Worksheet Full Data by “State” which is determined in Column E then by “Type of Project” in Column J and then by “Type of Product” in Columns L – Z.I need to determine the qty and the value.For example: my worksheet which is collating the data from worksheet "Full Data" looks a bit like this:```State A A Column B Column C Column D Column East End West end South End North end 1. Aged Care Qty Value Qty Value Qty Value Qty Value 2. Aquatic centre Qty Value Qty Value Qty Value Qty Value 3. Community Qty Value Qty Value Qty Value Qty Value State B A Column B Column C Column D Column East End West end South End North end 1. Aged Care Qty Value Qty Value Qty Value Qty Value 2. Aquatic centre Qty Value Qty Value Qty Value Qty Value 3. Community Qty Value Qty Value Qty Value Qty Value```I currently am using this formula to collate Value of all states as one total but I need to separate by individual States:By Value{=SUM(IF('Excel - Full Data'!\$J\$4:\$J\$2100="Aquatic Care",IF(MOD(COLUMN('Excel - Full Data'!\$L\$1:\$Z\$1),2)=0,IF(NOT(ISNA(MATCH('Excel - Full Data'!\$L\$4:\$Z\$2100,{"East End"},0))),'Excel - Full Data'!\$M\$4:\$AA\$2100))))}And all States by Qty:=SUMPRODUCT(('Excel - Full Data'!\$J\$4:\$J\$2100="Community")*(MOD(COLUMN('Excel - Full Data'!\$L\$1:\$Z\$1),2)=0)*(NOT(ISNA(MATCH('Excel - Full Data'!\$L\$4:\$Z\$2100,{"East End"},0))))*('Excel - Full Data'!\$M\$4:\$AA\$2100<>""))Any help would be appreciated.

Report •

#3
February 23, 2018 at 06:14:37
 I'm a bit confused. It looks like you've posted examples of the output from your formulas. Is that correct?If so, please keep in mind that we can't see your workbook from where we're sitting, so we don't know what your input data looks like. Reverse-engineering your formulas to try and figure out the input layout would be a task that I would not want to take on. ;-) Having the input layout helps us test solutions.In all honesty (and maybe it's because I can't see your input data) I'm not even sure what you are trying to accomplish. You said: "I need to separate by individual States:" When I look at the example data that you posted, it looks like it is already separated by states.What am I missing?message edited by DerbyDad03

Report •

Related Solutions

#4
February 27, 2018 at 15:08:01
 The example data is what I want to achieve. Currently I am only able to gather total data for all states as one.

Report •

#5
February 27, 2018 at 17:09:28