• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

Excel two conditional if's

J

jmmille

Junior Member
Ok guys,

I have an excel spreadsheet in which I need excel to go down a list of data, match 2 credentials and output a number in the row.

Here is a sample excel file I made to kinda give you the idea of what's going on.
http://www.bubism.com/excel.xls

In the worksheet named Data, I have a month, person, sales, and order column. In the other worksheet (Test), I need to search down the month column, match that credential, then match the person's name, then output the Sales cell in that row.

I have made the data random numbers just to make it easy to enter data.

On the Test page, I have attempted to right this formula myself in several forms. As you can see, the first one works perfectly. When I copy the formula around, it does not work anywhere else.

If anyone could provide any help with this, I'd be very appreciative.

Justin
 
Well, I wish I had noticed that your sales and orders were random numbers. I was wondering why the answer changed every time I recalculated. Anyway, this formula will pull in the correct data:

=IF($C4="orders",SUMPRODUCT((Data!$A$2:$A$22=$B4)* (Data!$B$2:$B$22=E$1)*Data!$C$2:$C$22), SUMPRODUCT((Data!$A$2:$A$22=$B4)* (Data!$B$2:$B$22=E$1)*Data!$D$2:$D$22))

Put that in D4 on Test and then copy it to the other cells. Obviously, you will need to tweak the ranges to work with your real data. Also, the way your worksheet is set up assumes that no two employees have the same name.

Test it yourself, but if I understood the question then it works. BTW, after generating the random data, you should have used Copy then Paste Special Values to stop it from changing.
 
Sorry, I forgot to mention that your application is perfect for Pivot Tables. Take a look at those. The Pivot Table will eliminate the need for the formula that I gave you. It will also make your life much easier. 🙂
 
Thank you Mayest!

That is exactly what I was trying to do. I'll definitely look into the pivot tables too!

PS: Sorry about the random numbers. I can't post the original so I had to make a mock-up 🙂
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top