Need simplified formula

[prasaddn]

Hi


=IF(X3="Non-ITES",AA3,0)+IF(AF3="Non-ITES",AI3,0)+IF(AN3="Non-ITES",AQ3,0)+IF(AV3="Non-ITES",AY3,0)+IF(BD3="Non-ITES",AG3,0)+IF(BL3="Non-ITES",BO3,0)





Well, I wanted to explain the my excel file structure but i might get myself lost explaining. let me try, actually, I have rows of data for each employee with upto 6 previous experience details column by column.


My data starts from row 3.


In columns below I have tracked type of industry, values.. IT, Non-ITES and BPO:

X

AF

AN

AV

BD

BL


In columns below I have tracked their experience in number of months..

AA

AI

AQ

AY

BG

BO


For certain limitation i cant change the format or add any new helper column, nor VBA. Hence looking for only formula based solutions.


I need to find the total number of months where the industry type = "Non-ITES".


Challenge is ... Well, you will understand when you place my formula in your excel sheet


Any pointers to simplify??


Regards,

Prasad DN

 

[prasaddn]

One more clarification I can provide.

data in columns

X - AA are of company 1

AF - AI are of company 2

AN - AQ are of company 3

AV - AY are of company 4

BD - BG are of company 5

BL - BO are of company 6


Hope this bring more clarity of my file.


Regards,

Prasad DN

 

[Luke M]

Assuming that "Non-ITES" isn't appearing in any other column, you can do:

=SUMIF(X3:BL3,"Non-ITES",AA3:BO3)


Since the two arrays of X3:B3 and AA3:BO3 are the same size, and the cells of interest are in the same relative positions, everything works out.

 

[prasaddn]

I should fire myself out !!


Always go behind complex formula and I forget basic ones. My bad


Thank you Luke M.


Regards,

Prasad DN

PS: have a great weekend.

 

[Luke M]

No worries Prasad, happens to the best of us.

Have a good weekend too!