Need a workable IFAND function formula for multiple criteria or maybe an Array formula?

1. Need a workable IFAND function formula for multiple criteria or maybe an Array formula?

Hi All,

I have rows of an order# in a column, rows of differing months that corresponds to the order number in another column, rows of different years that corresponds to the order# and months in another column and finally, rows of a sales percentage corresponding to the order#, months and years.

I have built a data validation list so that I can change order numbers and have the corresponding sales percentage change for the specific month and year as I change the order #'s.

The attached images shows how my data is laid out...

I have tried the following formula....

=if(and(\$B\$2(order#)=\$F\$2:\$F\$100(range of order#'s),D4(Month)=\$G\$2:\$G\$100(range of months),C5(Year)=\$H\$2:\$H\$100(range of years),\$I\$2:\$I\$100(range of sales %),"")

This formula returns blanks for everything. I am trying to populate the second spreadsheet with the order %'s so that I can create graphs.

Any help is appreciated.

Thanks.

2. Re: Need a workable IFAND function formula for multiple criteria

We cannot work with images. Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Because browsers behave differently, many of our members cannot see uploaded images. Do not upload a picture of your file as this will only delay getting solutions.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

3. Re: Need a workable IFAND function formula for multiple criteria

Thanks Ali.

I will do a mock up and repost.

4. Re: Need a workable IFAND function formula for multiple criteria

If I am not making it clear, please let me know and I will try my best to make it easier to understand as I need assistance in figuring this out today.

Thanks.

5. Re: Need a workable IFAND function formula for multiple criteria or maybe an Array formula

In I4

=SUMIFS(\$D:\$D,\$A:\$A,\$G\$1,\$B:\$B,I\$3,\$C:\$C,\$H4)

copy across and down

6. Re: Need a workable IFAND function formula for multiple criteria or maybe an Array formula

Thank you John!!!

Worked like a charm!

7. Re: Need a workable IFAND function formula for multiple criteria

Originally Posted by jeptik
If I am not making it clear, please let me know and I will try my best to make it easier to understand as I need assistance in figuring this out today.

Thanks.
Sorry - I didn't see that you had attached the file. Looks like John has sorted you out, though.

8. Re: Need a workable IFAND function formula for multiple criteria or maybe an Array formula

Thank you for the feedback and rep.

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

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1