• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

I need a formula to achieve a calculation based on a couple of things

Looking for a formula to look through column A and count the quantity of cells that contain the #12 "=COUNTIF(A1:A9)" in this case the answer is 6. I then need, if a cell in A did contain a 12 create a SUM based on the corresponding cell C. And then multiply the amount of Cells in A that contain 12 with the SUM of the corresponding Cells in C. In this case it would be 6x24. 6 being the amount of 12's, and 24 being the SUM of the #'s that exist in column C in a row that contained a 12. I hope a clarified that correctly. Please ask questions if need be. Thank you VERY much


      A        B       C
1  12      1      3
2  12      6      5
3    6      1      6
4    3      1      6
5  12      6      4
6  12      6      4
7  12      5      4
8  12      5      4
9    4      4      6
0
Mcottuli
Asked:
Mcottuli
  • 3
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Enter this in row 1 and copy it down

=COUNTIF(B:B,B1)*SUMIF(B:B,B1,D:D)
0
 
Harry LeeCommented:
ssaqibh,

small error on your formula.

=countif(A:A,12)*sumif(a:a,12,c:c)

Mcottuli is putting the column and row header just to show where the cells are. So the reference is one row and one column off. Also, if using A1 as the cell reference, it may cause error if a1 is not 12.
0
 
Saqib Husain, SyedEngineerCommented:
Yes, you are right. I simply copied the lines above to excel and then parsed them from where I got the extra column.
0
 
McottuliAuthor Commented:
Thank you very much, however I asked the question incorrectly. Was I need is just the SUM of column C where column A = 12. In other words, there is no need to multiply A and B. I just need to Identify anything in column C that has an A record of 12 and SUM up the C column. Apologize for the confusion.
0
 
Saqib Husain, SyedEngineerCommented:
=SUMIF(A:A,12,C:C)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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