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

**Jdog888** · 02-07-2024, 12:59 PM

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!

**BadlySpelledBuoy** · 02-07-2024, 01:05 PM

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

**Glenn Kennedy** · 02-07-2024, 01:17 PM

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.

**Jdog888** · 02-07-2024, 01:26 PM

Thank you Glenn! =)

**Glenn Kennedy** · 02-07-2024, 01:28 PM

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.

**Jdog888** · 02-07-2024, 01:31 PM

Thanks BadlyspelledBuoy! =)

**HansDouwe** · 02-07-2024, 03:09 PM

Another way (available from O365 V2401):

Please try:

Formula:

Please Login or Register  to view this content.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Copy one column, but loop through a second column multiple times depending on first column

[SOLVED] Copy column range in the same column multiple times based on cell value

How to match a column of date times with a column of times (and corresponding data)?

Count num of times data appears in one column, depending upon criteria in another column

[SOLVED] when updating sheet1 a1:a100, auto updates sheet2 a1:a100.

[SOLVED] count the number of times each string appears in a column and make a summary column

How-to SUM(A1:A100)-SUM(LARGE(A1:A100,{1,2,..,n})) with n being a variable?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions