INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

Hi

Quote:

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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.

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

alt+D
P

Quote (FAQ)

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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?

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

Quote:

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

Exactly what does that mean?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

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

(OP)
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.

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

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 winky smile). I’m guessing that that’s where you might have gone astray.

Let me know if this helps or not.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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.

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

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close