IF With 2 Criteria returning a Value or 0

[sea_captain]

I have a spreadsheet with thousands of rows with Projects and Hours and i'm trying to solve the "yellow cells"
I need to place the hours in column C "Non-Billable" (N-B) OR column D "Non-Working" (N-W) based on a Mapping Table I created by identifying each Project as either N-B or N-W

For example Project "ABC" is identified as "N-B" in the mapping table, I need the Hours to show up in column C and NOT D.

I've tried to create IF formulas but keep getting hung up.
Basically i want a formula in col. C and D that says, (in cell C2 and D2) IF "Project" (cell A2) matches the mapping table col. A:A AND the "Hour Type" (cell C$1 or D$1) = the mapping table (cells B8:B10), then $B2 or 0

Any Ideas or better ways to accomplish this?
Thanks for any help!
SC

 

[FormR]

Hi, perhaps something like this.

Book1
	A	B	C	D
1	Project	Hours	Non-Billable	Non-Working
2	ABC	8.6	8.6	0
3	123	7.2	0	7.2
4	A1B2C3	5.4	5.4	0
5
6	Mapping Tabe
7	Project	Hours Type
8	A1B2C3	Non-Billable
9	ABC	Non-Billable
10	123	Non-Working
Sheet1

Cell Formulas
Range	Formula
C2:D4	C2	=IF(VLOOKUP($A2,$A$7:$B$10,2,0)=C$1,$B2,0)

 

[Joe4]

Assuming that each Project only appears once in the Project Mapping:

Put this formula in cell C2 and copy down and across to D4:

=COUNTIFS($A$8:$A$10,$A2,$B$8:$B$10,C$1)*$B2

 

[sea_captain]

Hi, perhaps something like this.

Book1
	A	B	C	D
1	Project	Hours	Non-Billable	Non-Working
2	ABC	8.6	8.6	0
3	123	7.2	0	7.2
4	A1B2C3	5.4	5.4	0
5
6	Mapping Tabe
7	Project	Hours Type
8	A1B2C3	Non-Billable
9	ABC	Non-Billable
10	123	Non-Working
Sheet1

Cell Formulas
Range	Formula
C2:D4	C2	=IF(VLOOKUP($A2,$A$7:$B$10,2,0)=C$1,$B2,0)

I'll give it a try, thanks!

 

[sea_captain]

Assuming that each Project only appears once in the Project Mapping:
View attachment 121357

Put this formula in cell C2 and copy down and across to D4:

=COUNTIFS($A$8:$A$10,$A2,$B$8:$B$10,C$1)*$B2

unfortunately the project "name" is used by multiple 'departments', so it appears more than once.

 

[Joe4]

unfortunately the project "name" is used by multiple 'departments', so it appears more than once.

In your lower section?
If so, could there ever be a conflict, i.e. where "ABC" says "Non-Billable" for one record, and "Non-Working" for another?
If that could happen, how do you know which one to choose/use?

 

[sea_captain]

your lower section?
If so, could there ever be a conflict, i.e. where "ABC" says "Non-Billable" for one record, and "Non-Working" for another?
If that could happen, how do you know which one to choose/use?

Sorry, i misread. the 'projects' are unique in the mapping table (correct)
In the 'source' tab, it's not unique.

I think the first solution solved my issue. but i will try yours as well

 

[Joe4]

Sorry, i misread. the 'projects' are unique in the mapping table (correct)
In the 'source' tab, it's not unique.

I think the first solution solved my issue. but i will try yours as well

Yes, there are often many different ways to solve the same problem.
As long as the "Projects" are unique in the Mapping table, it should work.

 

[sea_captain]

Thanks you FormR and Joe4. As of now, both solutions work! I'll mark this as 'solved'!
Thanks again!