+ Reply to Thread
Results 1 to 7 of 7

IF column A1:A100=1 how many times does column B1:B100=1

  1. 02-07-2024, 12:59 PM #1
    Jdog888
    Jdog888 is offline
    Registered User
    Join Date
    02-07-2024
    Location
    Canada
    MS-Off Ver
    newest
    Posts
    3

    IF column A1:A100=1 how many times does column B1:B100=1

    Hello everyone, I am new to this forum and I am also completely new to learning excel, I am trying to create a formula as follows:

    I have 2 columns, column A and Column B, I am trying make a formula so that if A1:A100=1 how many occurrences does B1:100=1. Then from there continue if A1:A100=1 how many occurrences does B1:100=2.



    I created this formula but it didn't work: =COUNTIFS(A10:B100, "1", B10:B100,"1")




    If anyone could let me know how to create a formula for this if its even possible then that would be greatly appreciated!

    Have a great day everyone!
    Reply With QuoteRegister To Reply
  2. 02-07-2024, 01:05 PM #2
    BadlySpelledBuoy
    BadlySpelledBuoy is offline
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: IF column A1:A100=1 how many times does column B1:B100=1

    Hi and welcome

    If your data in A and B is numbers rather than text, remove the quite marks from your formula and it should work.

    If not, attach a sample workbook with your formula in place and we can take a look from there.

    BSB
    Reply With QuoteRegister To Reply
  3. 02-07-2024, 01:17 PM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: IF column A1:A100=1 how many times does column B1:B100=1

    Do it all in one go. Delete all expected results forst.

    =LET(A,A1:A100,B,B1:B100,C,SORT(UNIQUE(A)),D,TRANSPOSE(SORT(UNIQUE(B))),E,REDUCE(C,D,LAMBDA(y,z,HSTACK(y,BYROW(C,LAMBDA(x,ROWS(FILTER(A,(A=x)*(B=z)))))))),VSTACK(HSTACK("ColA",D),E))

    see file.
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  4. 02-07-2024, 01:26 PM #4
    Jdog888
    Jdog888 is offline
    Registered User
    Join Date
    02-07-2024
    Location
    Canada
    MS-Off Ver
    newest
    Posts
    3

    Re: IF column A1:A100=1 how many times does column B1:B100=1

    Thank you Glenn! =)
    Reply With QuoteRegister To Reply
  5. 02-07-2024, 01:28 PM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: IF column A1:A100=1 how many times does column B1:B100=1

    You're welcome. Thanks for letting us know that you got an answer. I couldn't think of a nice way to label the top row as "column B values" automatically....



    Please take a moment and consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution here today.

    Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  6. 02-07-2024, 01:31 PM #6
    Jdog888
    Jdog888 is offline
    Registered User
    Join Date
    02-07-2024
    Location
    Canada
    MS-Off Ver
    newest
    Posts
    3

    Re: IF column A1:A100=1 how many times does column B1:B100=1

    Thanks BadlyspelledBuoy! =)
    Reply With QuoteRegister To Reply
  7. 02-07-2024, 03:09 PM #7
    HansDouwe
    HansDouwe is offline
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,427

    Re: IF column A1:A100=1 how many times does column B1:B100=1

    Another way (available from O365 V2401):

    Please try:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy one column, but loop through a second column multiple times depending on first column
    By redapplesonly in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-08-2019, 10:12 AM
  2. [SOLVED] Copy column range in the same column multiple times based on cell value
    By PaulM100 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-15-2019, 06:22 AM
  3. How to match a column of date times with a column of times (and corresponding data)?
    By jbody in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-19-2016, 02:40 PM
  4. Count num of times data appears in one column, depending upon criteria in another column
    By lsmidt in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-04-2013, 07:59 PM
  5. [SOLVED] when updating sheet1 a1:a100, auto updates sheet2 a1:a100.
    By chestersneakers7 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-15-2013, 08:09 AM
  6. [SOLVED] count the number of times each string appears in a column and make a summary column
    By intothewild in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-04-2012, 08:02 AM
  7. How-to SUM(A1:A100)-SUM(LARGE(A1:A100,{1,2,..,n})) with n being a variable?
    By Coume in forum Excel General
    Replies: 5
    Last Post: 03-22-2010, 01:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1