# Thread: If statements with two critera

1. ## If statements with two critera

In the attached worksheet sample, Using the data on the left (A1 - F 92)and using an "IF" STATEMENT. to match the LOCATION # with the TYPE and RECORD the % amount from column D onto the table (K2 - R 12) in the appropriate cell.

I do not want to use the copy and transpose for the data is voluminous and would not be feasible.

Any formula suggestions?

Thanks

2. in K2:
=SUMPRODUCT((\$A\$2:\$A\$92=\$J2)*(\$B\$2:\$B\$92=K\$1)*\$D\$2 :\$D\$92)

Copy across the row and down the colum

Steve

3. I will give this a try but why wouldn't an if statement with the specific criteria of location and type be better and more exact.

The other reason I prefer this to sumproduct is because I am not sure how the SUMPRODUCT work. Any insights on this?

Thanks

4. I tried the sumproduct formula from above on the actual workbook. it populated with 0 only. the difference is the data is on a separate worksheet from the result or target worksheet. I do not think that that should be an issue, how ever it recorded a zero 0 under the type on the target worksheet.

Any ideas??

5. What is different about the actual workbook compared to the sample? It works in the sample workbook?

Concerning an IF statement over the sumproduct, I used the sumproduct since it uses array implicitly without having to explicit indicate to excel (using control-shift-enter) to determine it. You can do it with an IF (confirm with ctrl-shift-enter)
=SUM(IF((\$A\$2:\$A\$92=\$J2)*(\$B\$2:\$B\$92=K\$1),\$D\$2:\$D\$ 92))

or even a SUMIFS:
=SUMIFS(\$D\$2:\$D\$92, \$A\$2:\$A\$92,\$J2,\$B\$2:\$B\$92,K\$1)

They all should be getting the same number. SUMIFS is probably the most efficient. I used the SUMPRODUCT out of habit, but it is more general as it can be used in excel versions before XL2007.

Steve

6. Note: A pivot table could be used to create the Location / Type table directly...

Steve

7. Thank you. sometimes you can't see the forest because of the bushes!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•