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

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

(OP)

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.

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

Not so!

=SUMIF(

CriteriaRange,Criteria,SumRange)...the second argument is for your

criteria.Assuming you table has 10 rows...

=SUMIF(A2:A11,"zzz",C2:C11)

...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...

=SUMPRODUCT((A2:A11="zzz")*(C2:C11))

...and the order of expressions is irrelevant. This works just as well...

=SUMPRODUCT((C2:C11*(A2:A11="zzz"))

This also works much, MUCH better with > < operators. Suppose column B were dates and you ALSO needed dates between two values in D1 & D2...

=SUMPRODUCT((A2:A11="zzz")*(B2:B11>=D1)*(B2:B11<=D2)*(C2:C11))

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,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel- Sum only the values in column 3 which meet the exact criteria of colum 1

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

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

formulais...G5:

=SUMPRODUCT((Description=$F5)*(INDIRECT(G$1)))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: NORMALIZE Your Table using the PivotTable Wizard.

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,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel- Sum only the values in column 3 which meet the exact criteria of colum 1

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

P

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel- Sum only the values in column 3 which meet the exact criteria of colum 1

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

Exactly what does that mean?

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel- Sum only the values in column 3 which meet the exact criteria of colum 1

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

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

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.

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

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,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel- Sum only the values in column 3 which meet the exact criteria of colum 1

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.

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

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,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel- Sum only the values in column 3 which meet the exact criteria of colum 1

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

Thanks Skip!