CODEWITHSUNDEEP
vlookupsumifs
NFS63
NFS63

Reputation: 9

SUMIF calculation with little complication on my side

Please see the file

https://drive.google.com/file/d/0BzuFuPfITC9IeVNydUtJWEVHSFE/view?usp=sharing

I would like to have the current formula (without change) working but only with selection of the country name. so for example, when B1 is UK, it will do the rest of the formula for rows with UK in Column "E" on Qty tab.

Please write the formula on the file.

I hope I've explained my question well enough.

Appreciate your help in advance. Thanks.

Upvotes: 0

Views: 54

Answers (1)

Thomas Landauer
Thomas Landauer

Reputation: 8355

Forget about =IF(ISNA(VLOOKUP...))! What you really want is SUMIFS(). This is the formula for B2:

=SUMIFS(Data!F:F, Data!C:C, A2, Data!E:E, $B$1)

And if you want it to be draggable onto column C right away, just modify some $'s, like this:

=SUMIFS(Data!$F:$F, Data!$C:$C, $A2, Data!$E:$E, B$1)

As @trincot says, you need to change UK to GB first to see it work ;-)

EDIT after your comment:
Sure, just wrap another IF around it:

=IF(B1="GB";IF(ISNA(VLOOKUP(A:A, Data!C:F, 4, 0)), "0",SUMIF(Data!C:C, Master!A2, Data!F:F));"Whatever")

Upvotes: 1

Related Questions