Excelnoob1995

New Member
Hi,

I'm trying to make a column that will be used to inform a pivot table, however because the data sheet is populated by dates and not numbers, if i take data from both columns for the pivot table, there will be an overlap and inflation of resulting totals where both columns contain information. I think I need an ISNUMBER formula that will produce a text / result IF either column has information, or if they both do.

So it needs to do something like

IF CELL-A = (ISNUMBER) AND CELL-B = (ISNUMBER) THEN "YES"
IF CELL-A = (ISNUMBER) AND CELL-B = ("") THEN "YES"
IF CELL-A = ("") AND CELL-B = (ISNUMBER) THEN "YES"
IF CELL-A = ("") AND CELL-B = (I"") THEN ""

If anyone can help, this would be really great!

Kind regards,

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
IF CELL-A = (ISNUMBER) AND CELL-B = (ISNUMBER) THEN "YES"
IF CELL-A = (ISNUMBER) AND CELL-B = ("") THEN "YES"
IF CELL-A = ("") AND CELL-B = (ISNUMBER) THEN "YES"
IF CELL-A = ("") AND CELL-B = (I"") THEN ""

=IF( AND(A1="", B1=( I"") , "",
BUT not sure what ( I"") means - is that just a PIPE
=IF( AND(A1="", B1="I") , "", IF( OR ( AND ( ISNUMBER(A1), ISNUMBER(B1)) , AND( ISNUMBER(A1), B1="") , AND ( A1="", ISNUMBER ( B1))),"yes", ""))

I have setout as you have written , but there maybe ways to simplify or use a IFS()

=IF(AND(A1="",B1="I"),"",IF(OR(AND(ISNUMBER(A1),ISNUMBER(B1)),AND(ISNUMBER(A1),B1=""),AND(A1="",ISNUMBER(B1))),"yes",""))

Replies
18
Views
501
Replies
1
Views
141
Replies
1
Views
167
Replies
11
Views
483
Replies
10
Views
473

1,203,759
Messages
6,057,198
Members
444,913
Latest member
ILGSE

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.

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

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