# Excel macro or formula help

Excel macro or formula help
Attach file has users who have softwares

Where ever Yes it has to check the other sheet and calculate the cost and update the sheet

LVL 11
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
=IF(N2="YES",12000,0)+IF(O2="YES",2000,0)+IF(P2="YES",3000,0)+IF(Q2="YES",33000,0)+IF(R2="YES",2000,0)+IF(S2="YES",1000,0)+IF(T2="YES",33000,0)

Drag it to rest of the cells so that the formula applies for each row
EngineerCommented:
You can use this ARRAY formula

=SUMPRODUCT((N2:V2="Yes")*TRANSPOSE(Cost!\$B\$1:\$B\$9))

in row 2 and then copy down. But you will have to make sure that the costs on cost sheet are numbers and not text+numbers. see attached
Copy-of-cost.xls
Commented:
This version can be used to avoid "array entry"

=SUMPRODUCT(MMULT((N2:V2="Yes")*1,Cost!\$B\$1:\$B\$9))

regards, barry

Experts Exchange Solution brought to you by