Need to tag multiple rows the same, however attributes differ except for ID (nested IF?)

Occasional Visitor

I have a file of project lines where multiple lines in the file may constitute a single project (col H has consistent budget ID) and that project may include the use of technology + services (col L shows revenue type). Some projects are linked to budgets and some are not.


The question I am trying to solve for is: for projects that are linked to budgets - if a project contains the sale of technology, how can I tag all the associated project lines (technology + services) as "Y" for "project includes tech (col Q; highlighted yellow)?".

Right now the formula is created to only identify the lines with actual technology revenue so total projects utilizing technology is a skewed number.

Please see attached workbook showing 3 sample projects: two with linked budgets - one including tech + services and one services only and one project not linked to a budget (this should be a "N" for "project includes tech" because the linked budget has the info to determine tech/services breakdown). I am attempting to populate col Q with the desired output which should be "Y" for the first 4 lines and "N" for the remaining lines.

Appreciate any help - I believe I can get there with a nested IF and that's the approach I'm currently working on personally, but would be great to hear other ideas.

2 Replies

@jessg0121 

 

I believe I can get there with a nested IF and that's the approach I'm currently working on personally, but would be great to hear other ideas.

 

I'm not following the combination of conditions well enough to give you the final formula -- just a little too confusing for an outsider -- but I think rather than the nested IF approach, it sounds to me as if you should consider something along the lines of

=IF(AND(Cond1,Cond2),Consequence,Else)

@jessg0121 

=IF(SUMPRODUCT((H2=$H$2:$H$11)*($L$2:$L$11="Technology"))>0,"Yes","No")

Does this return your expected result?

project id.JPG