Returning a comment within three columns

Yusuf

Active Member
Hi all

I would like to return a comment "Complete" from data in three columns in a pivot table.
The problem is that not all accounts have the first item

Eg
Col A - Account nos
Col B - Pens
Col C - Pens Collected
Col D - Pencils
Col E - Pencils Collected
Col F - Pencil Boxes
Col G - Pencil Boxes Collected
(You can round the figures off to 1's and 2's if it makes things easier)

The reason I have the number of items first is sothat I can see how many were issued and compare those collected
Also, some of the accounts don't have "Pens" and of course "Pens Collected" so my predicament is that I would like a formula on the right of the table that states;

If B5="" then ignore B & C but return "Acc Closed" if D5=E5 and F5=G5

I am tasked of producing a figure that shows the accounts closed by the accounts issued only if all the criteria are met
I will then count the "Acc Collected" and work a percentage by the total acc's.

PS - If you have any suggestions and other ways that this result is possible then I'll really appreciate it

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Code:
``=IF(AND(\$B2="", AND(\$D2=\$E2, \$F2=\$G2)),"acc closed", "open")``
this formula will do the thing you requested in you pseudo code
If B5="" then ignore B & C but return "Acc Closed" if D5=E5 and F5=G5
Im not sure what your complete formula would look like. Should it measure B & C too when they are not empty?

=IF(B1-C1+D1-E1+F1-G1=0,"Complete","Not Complete")

Awesome guys, thanks Very much.

Cheers
Yusuf

Replies
3
Views
334
Replies
6
Views
242
Replies
0
Views
269
Replies
2
Views
188
Replies
0
Views
584

1,207,255
Messages
6,077,313
Members
446,278
Latest member
hoangquan2310

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