[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

Excel conditional adding of cells

I receive a spread sheet with a field with mixed case and more characters than needed.
I have cleaned it up and now would like to add the values in column D when the values in column A are equal and also the values in Column C.
Some times there is only one value in column A and sometimes there are several.
Is there an IF statement that will work by copying into each cell or is a more involved macro needed.
0
maryj152
Asked:
maryj152
  • 3
  • 2
  • 2
  • +2
1 Solution
 
Shanan212Commented:
Can you post a sample please?
0
 
maryj152Author Commented:
Thought I had attached a part of the file, guess not.
monthlyWB.xlsx
0
 
Saqib Husain, SyedEngineerCommented:
Enter this in A1 and copy down

=SUMIF(A:A,A1,D:D)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Saqib Husain, SyedEngineerCommented:
Or for more clarity use

=IF(A1<>A2,SUMIF(A:A,A1,D:D),"")
0
 
Shanan212Commented:
You could use a pivot table to do this easily

Let me know if you need clarification
Copy-of-monthlyWB.xlsx
0
 
Ken ButtersCommented:
first let me restate what I think you are asking for...

Sum up the values in column D.... where the values in column A .... AND the values in Column C match?

If that is what you want you can use the following formula in column E1 and fill down.

=SUMPRODUCT(--(A:A=A1),--(C:C=C1),(D:D))
0
 
maryj152Author Commented:
Is there any way to enter the sum only once? If not, no problem. This does what I need.
0
 
barry houdiniCommented:
Try this version so that you don't get repeat values, SUMIFS is more efficient than SUMPRODUCT, too

=IF(COUNTIFS(A$1:A1,A1,C$1:C1,C1)=1,SUMIFS(D:D,A:A,A1,C:C,C1),"")

regards, barry
0
 
maryj152Author Commented:
Great thanks, but I already gave away all of the points.
0
 
barry houdiniCommented:
No problem

regards, barry
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now