Sum of matches from column A to column B
Closed
Jason
-
Apr 27, 2010 at 08:13 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Apr 30, 2010 at 06:43 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Apr 30, 2010 at 06:43 AM
Related:
- Sum of matches from column A to column B
- Excel copy column from one sheet to another automatically - Guide
- Sum column if another column equals ✓ - Excel Forum
- Count the number of times a value appears in a column ✓ - Office Software Forum
- We can’t give you access to this account or help with your request until we receive an accepted form of id that matches the information listed on the account. learn more about the types of id we accept in the help center: ✓ - Facebook Forum
- Sum if greater than and less than cell value ✓ - Excel Forum
6 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Apr 27, 2010 at 10:29 PM
Apr 27, 2010 at 10:29 PM
it is not clear what you want suppose data is like this (A1 to B9)
hdng1 hdng2
2 4
3 3
1 1
1 2
3 1
1 1
2 3
3 4
do you want the number of 1 in both A2 to A9 abd B2 to b9 which is 3+3=6 then use this formula
=COUNTIF(A2:A9,"1")+COUNTIF(B2:B9,"1")
if you want the no, of combination of 1 1 in A andB column then it will be 2 for which you use the formula
=SUMPRODUCT((A2:A9=1)*(B2:B9=1))
hdng1 hdng2
2 4
3 3
1 1
1 2
3 1
1 1
2 3
3 4
do you want the number of 1 in both A2 to A9 abd B2 to b9 which is 3+3=6 then use this formula
=COUNTIF(A2:A9,"1")+COUNTIF(B2:B9,"1")
if you want the no, of combination of 1 1 in A andB column then it will be 2 for which you use the formula
=SUMPRODUCT((A2:A9=1)*(B2:B9=1))
Sorry,let me further explain.
The formula will go through each numbers in Column A and see if it exist in Column B. It the number exist, the counter will count that match. So for your data sample, the counter should display "8" since all the numbers in hdng1 are listed in hdng2.
Can the criteria in a COUNTIF be defined with more than one value or an area of data? In your formula the criteria is set to "1", I would like to set the criteria to the numbers listed in hdng2 (B2:B9).
The only way I was able to get things to work was to create 2 formulas. If the number if Column A (A2) matches a number in Column B (B2:B9), a COUNTIF statement will mark a "Yes" or a "1" in column C for that row (C2). I'll then COUNT/SUM all the "Yes" or "1" in column C. This defeats the purpose because I need to do this in a single formula, where the it checks for matches and does the counting as well.
Thanks for your help!
The formula will go through each numbers in Column A and see if it exist in Column B. It the number exist, the counter will count that match. So for your data sample, the counter should display "8" since all the numbers in hdng1 are listed in hdng2.
Can the criteria in a COUNTIF be defined with more than one value or an area of data? In your formula the criteria is set to "1", I would like to set the criteria to the numbers listed in hdng2 (B2:B9).
The only way I was able to get things to work was to create 2 formulas. If the number if Column A (A2) matches a number in Column B (B2:B9), a COUNTIF statement will mark a "Yes" or a "1" in column C for that row (C2). I'll then COUNT/SUM all the "Yes" or "1" in column C. This defeats the purpose because I need to do this in a single formula, where the it checks for matches and does the counting as well.
Thanks for your help!
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Apr 28, 2010 at 11:34 PM
Apr 28, 2010 at 11:34 PM
will you be happy with a macro . I am trying to get a formula.
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Apr 28, 2010 at 11:40 PM
Apr 28, 2010 at 11:40 PM
suppose data is like this (a1 to B7)
1 2
2 3
3 4
4 5
5 6
6 7
7 8
now in C1 copy this formula
=COUNTIF($B$1:$B$7,A1)
copy C1 down
in an empty cell copy this formua
=COUNTIF(C1:C7,">0")
you will get 6
will this helpl?
1 2
2 3
3 4
4 5
5 6
6 7
7 8
now in C1 copy this formula
=COUNTIF($B$1:$B$7,A1)
copy C1 down
in an empty cell copy this formua
=COUNTIF(C1:C7,">0")
you will get 6
will this helpl?
Didn't find the answer you are looking for?
Ask a question
I was able to find a macro that does the function but I want to avoid the macro because the user will have to "run" the macro every time they want to generate the information.
Your second method works. That's the only solution I was able to get too (see 3rd paragraph). The problem is that I'm looking for a single formula. Using this solution, I have to enter a formula on every row and do a count. I want to avoid entering formulas on the spreadsheet the users are entering data on.
The problem I notice is that "criteria" in the formulas need to be a singular entry of data (i.e, the criteria in the COUNTIF is set to "A1"). If there was a way to define the criteria as an area of list of data or an array (i.e =COUNTIF(B1:B7,A1:A7), it would solve the problem.
I really appreciate your help! I know I've spent way too much time on this.
Your second method works. That's the only solution I was able to get too (see 3rd paragraph). The problem is that I'm looking for a single formula. Using this solution, I have to enter a formula on every row and do a count. I want to avoid entering formulas on the spreadsheet the users are entering data on.
The problem I notice is that "criteria" in the formulas need to be a singular entry of data (i.e, the criteria in the COUNTIF is set to "A1"). If there was a way to define the criteria as an area of list of data or an array (i.e =COUNTIF(B1:B7,A1:A7), it would solve the problem.
I really appreciate your help! I know I've spent way too much time on this.
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Apr 29, 2010 at 10:19 PM
Apr 29, 2010 at 10:19 PM
I tried and I am still not able to find a single formula. I shall try later .
1. you need not enter the first formula in the data sheet area of user.
you can do that outside their area.
for e.g you type the first formula as it is in for e.g D15 and copy D15 as many rows of data available in the main data portion
then at the end of this type the second formula.
2. if you have a macro why not use it. If the user is not familiar with running macro, you can create a button on the sheet (from form toolbar) and assign this macro to that button. What the user has to do is to JUST CLICK THE BUTTON. This should not be difficult to any body even if he/she uses excel even if it is just to enter data. If you want to make still more automatic then you can create an event code to run the macro when the user saves the file.
1. you need not enter the first formula in the data sheet area of user.
you can do that outside their area.
for e.g you type the first formula as it is in for e.g D15 and copy D15 as many rows of data available in the main data portion
then at the end of this type the second formula.
2. if you have a macro why not use it. If the user is not familiar with running macro, you can create a button on the sheet (from form toolbar) and assign this macro to that button. What the user has to do is to JUST CLICK THE BUTTON. This should not be difficult to any body even if he/she uses excel even if it is just to enter data. If you want to make still more automatic then you can create an event code to run the macro when the user saves the file.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 30, 2010 at 06:43 AM
Apr 30, 2010 at 06:43 AM
my two cents. If you have a macro, and you dont want user to run macro, convert sub routine to a function and use that function on sheet. Basically use a UDF (user defined function)
