Excel- Sum only the values in column 3 which meet the exact criteria of colum 1 1

[pro356]

I have a spreadsheet I am trying to build as template for a budget. In one tab I have listed all our customers and the budgeted amount of income per month. Included in that tab is a column showing the type of income for each client (which income account) by month, it could be one of 6 different types. I have another tab which shows the budget totals by account (a summary for each of the 6). How can I create a formula that will sum for each monthly column all those with income account A and post that in the budget tab with totals, ditto for B, etc. I tried the Sumif but that apparently only looks at the column with the values. I need to be able to sum that value column if the value in a separate column (the account name) matches.

 

[SkipVought]

Hi

I tried the Sumif but that apparently only looks at the column with the values

Not so!
[tt]
=SUMIF(CriteriaRange,Criteria,SumRange)
[/tt]
...the second argument is for your criteria.

Assuming you table has 10 rows...
[tt]
=SUMIF(A2:A11,"zzz",C2:C11)
[/tt]
...where “zzz” is your account of interest.

I much prefer using the SUMPRODUCT() function to SUMIF(), SUMIFS(), COUNTIF(), COUNTIFS(). Each time I think I might want to use SUMIF(), SUMIFS(), COUNTIF(), COUNTIFS(), I know that I'll need to look up HELP because none of them are at all intuitive. HOWEVER, SUMPRODUCT() is very intuitive. In this case...
[tt]
=SUMPRODUCT((A2:A11="zzz")*(C2:C11))
[/tt]
...and the order of expressions is irrelevant. This works just as well...
[tt]
=SUMPRODUCT((C2:C11*(A2:A11="zzz"))
[/tt]

This also works much, MUCH better with > < operators. Suppose column B were dates and you ALSO needed dates between two values in D1 & D2...
[tt]
=SUMPRODUCT((A2:A11="zzz")*(B2:B11>=D1)*(B2:B11<=D2)*(C2:C11))
[/tt]

However, if we saw a representation of your data, there might be another method. Plz post a representative sample of table or upload your workbook.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pro356]

See the attached file. The goal is sum the amounts in the Income tab in the cell in the Budget tab for each Income type.

 

[SkipVought]

Here's your workbook back with these modifications:
1) Your Income table has been assigned Named Ranges based on column headings
2) on the 2018 Budget sheet, I added text values in row 1 that correspond to the Named Ranges of the month values.
3) the resulting formula is...
[tt]
G5: =SUMPRODUCT((Description=$F5)*(INDIRECT(G$1)))
[/tt]

However, your Income table data is not normalized. It is a summary report and consequently is not as conducive for analysis as a normalized table would be. I created a normalized table on Sheet2 based on daata in Sheet1 using this process faq68-5287.

Then, using this new table, which is also a Structured Table, I demonstrated what that solution would look like below your results on 2018 Budget.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pro356]

Thanks. I didn't get very far trying to recreate this. I am using Excel 2016, and my option for a Pivot Table is thru Insert/Pivot Table but there is no option for "Multiple Consolidation Ranges". It only asks to Select a table or range, then choose where it is to be placed. I see no option to follow the steps you outline.

 

[SkipVought]

alt+D
P

1) Start the wizard -- Data/PivotTable & PivotChart Report...
If you have Excel Version 2007+ use alt+D P to activate the PT Wisard

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pro356]

I have replicated what you did but it is still not working for me. I can't post my actual file publicly as it is private company financial information. Is there a way to circumvent this?

 

[SkipVought]

...but it is still not working for me.

Exactly what does that mean?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Some time ago I tested unpivoting table with pivot table in excel 2016, without sussess, maybe I missed something.
Hopefully in excel 2016 there is native get & transform feature to unpivot tables (or power query add-in in excel 2010-2013). See office blog for details.

combo

 

[pro356]

To Skip,

I entered everything exactly as you had it in your file to Normalize, etc. but it didn't sum they way yours did. I can't see where ZI am making any mistake but who knows. I am at a loss to explain it.

 

[SkipVought]

My starting point was on Sheet1. In Sheet1 I show your Income table with a column inserted between the Row data and Column data (that is between Customer, Description and Date/Values). In this column, you will notice, I concatenate Customer & Description in each row, separated by a delimiter. I end up with ONE column of Row data and multiple columns of Column data that this process will transform into two columns of values.

At this point perform alt+D P and when asked, Select the data from the concatenated column eastward (unless your forward orientation is not a northward heading, in which case, reorient ;-)). I’m guessing that that’s where you might have gone astray.

Let me know if this helps or not.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pro356]

That was very helpful. I did misunderstand that. Still think I am missing something.

In step 4 there is a question "How many page fields do you want?" which defaults to "0" and I didn't change that. There are options to choose the item labels but when leaving page fields as zero there is no option to assign labels. Am I supposed to select more than zero and if so, how many, and how item labels should be entered?

As it is when I leave it as zero and hit next it pops up another window "Where do you want to put the PivotTable report" with options for New or Existing worksheet. It defaults to New and when I click Finish it creates a new table with the Customer & Service concatenated column still together, not in 2 columns like yours. Thus when I double click the bottom right cell it creates the other new table with the list like yours but still has the Customer | Service field concatonated.

 

[SkipVought]

Thus when I double click the bottom right cell it creates the other new table with the list like yours but still has the Customer | Service field concatonated.

Quite correct. The reason that we concatenated the columns with a delimiter, is that at this point in the process, we can expand the data from one column into, in this case, two columns using Data > Text to columns...

Of course, you must make room for Text to columns..., ie insert the appropriate number of columns, before executing this part of the process.

BTW, concatenating columns and expanding columns is not really part of the Normalization process, but it is using other Excel features in order to accomplish the Normalization process that requires ONE Row column.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JVFriederick]

I gave a star because the explanation why SUMPRODUCT function is advantageous is very good, I have not used it frequently in the past, but now plan to change.

Particularly the part about > < operators within the formula, something that is otherwise very difficult to do.

Thanks Skip!