Writing a statement counting multiple columns

datadummy

Active Member
Joined
Mar 16, 2017
Messages
312
Office Version
  1. 365
Platform
  1. Windows
I need help writing a statement that essentially says if column A is the month of December in the following format xx/xx/xxxx. Then if column B has a number that starts with 23, 24, 25. Then if column O is Y or N.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Use the following formula

[TABLE="class: grid, width: 950"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]P2[/TD]
[TD]=IF(AND(MONTH(A2)=12,OR(LEFT(B2,2)="23",LEFT(B2,2)="24",LEFT(B2,2)="25"),OR(O2="Y",O2="N")),"Match","NO Match")[/TD]
[/TR]
</tbody>[/TABLE]

Test and tell me.
 
Upvote 0
It doesn't seem to be working, here is my formula
=IF(AND(MONTH(A23)=12,OR(LEFT(B23,2)="23",LEFT(B23,2)="24",LEFT(B23,2)="25"),OR(O23="Y",O23="N")),"Y","N")
The data in the cells referenced is as follows
A2=12/4/2018
B2=237-1
O2=N
The equation is yielding a Y result when it should be an N.Please let me know if I have entered something incorrect.
 
Last edited:
Upvote 0
Let's see

=IF(AND(MONTH(A23)=12,OR(LEFT(B23,2)="23",LEFT(B23,2)="24",LEFT(B23,2)="25"),OR(O23="Y",O23="N")),"Y","N")


A23=
12/4/2018 Month = 12 Result = Yes
B23=237-1 left("237-1",2) = 23 Result =
Yes
O23="N" N=N Result =
Yes


We have 3 yes then the result is Y. If we had less than 3 yes then the result is N.

Or maybe I did not understand what the conditions are for getting Y
 
Upvote 0
So the first 2 points are accurate of yielding a Y but if column O has an N in it then I need the result to be an N,but if column O has a Y in it then the result should be Y
 
Upvote 0
I need help writing a statement that essentially says if column A is the month of December in the following format xx/xx/xxxx. Then if column B has a number that starts with 23, 24, 25. Then if column O is Y or N.


It says if column O is "Y" or "N".

Then it should be, if column O is "Y"

Test:

=IF(AND(MONTH(A23)=12,OR(LEFT(B23,2)="23",LEFT(B23,2)="24",LEFT(B23,2)="25"),O23="Y"),"Y","N")
 
Upvote 0
=IF(AND(MONTH(A23)=12,OR(LEFT(B23,2)="23",LEFT(B23,2)="24",LEFT(B23,2)="25"),O23="Y"),"Y","N")
You can shorten your formula slightly...

=IF(AND(MONTH(A2)=12,OR(LEFT(B2,2)={"23","24","25"}),O2="Y"),"Y","N")
 
Last edited:
Upvote 0
Would it be possible to attempt a wrap up statement that would say something like if column A = the month of December in this format xx/xx/xxxx and if column B has the 23,24,25 in them and then if column O is Y.
 
Upvote 0
Would it be possible to attempt a wrap up statement that would say something like if column A = the month of December in this format xx/xx/xxxx and if column B has the 23,24,25 in them and then if column O is Y.
Why are you trying to force the "in this format xx/xx/xxxx"? Dates in Excel are just whole numbers (the count of days since "day 0"... the day before January 1, 1899), what you see in the cell is for human convenience... Excel does not use it.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top