CODEWITHSUNDEEP
excel
Gabong
Gabong

Reputation: 68

MS EXCEL: COUNTIFS

just want to ask assistance on this one. How can I get the number active SKU in Sheet1 tab have a change in cost after matching against Sheet2?

I used multiple vlookups on this one and I'm not satisfied because of too many formulas, What I want is to have a single formula only .. coz i just want to get only the count.

I tried countifs but I'my stucked.. I'm not familiar yet with that functions.. I'm also want to know if the countif is capable in comparing two list from different sheets and how. TIA :)

Sheet 1 

SKU                 ID   Cost        Status
Product 12416       N03  10.6875     Inactive
Product 13129       N04  11.5875     Active
Product 12597       N05  11.3175     Active
Product 13128       N10  13.5675     Active
Product 13127       N11  15.3225     Active
Product 13126       N12  18.1125     Active
Product 13125       N13  13.7025     Active
Product 13130       N14  19.1475     Inactive
Product 13123       R65  12.285      Active

Sheet 2

Cost      Current Stock     ID
  12.87      99999          ZA02
  5.7        99999          N04
  13.74      99999          N10
  6.93       99999          N03
  3.46       99999          XL5-F
  2.47       99999          XL5-7
  1.74       99999          N11
  1.74       99999          N12

Upvotes: 0

Views: 42

Answers (2)

basic
basic

Reputation: 11968

Probably, the following formula may help:

=SUMPRODUCT((I2=$B$2:$B$10)*(G2<>$C$2:$C$10)*("Active"=$D$2:$D$10))

enter image description here

Upvotes: 0

StoneGiant
StoneGiant

Reputation: 1497

Here's what I think you are asking:

If the product in Sheet1 is active and the cost for the item in Sheet2 is different, you want to count the item. Or, to say it another way, you want a total of all Active products that are changing costs.

One way to do that is with an array formula. You create an array formula by typing the formula into the cell and hitting Ctrl + Shift + Enter.

Here's the formula:

= SUM(
      (IF( ISERROR( MATCH( B2:B10
                          ,Sheet2!$C$2:$C$9
                          ,0
                         )
                  )
          ,C2:C10
          ,INDEX( Sheet2!$A$2:$A$9
                 ,MATCH( B2:B10
                        ,Sheet2!$C$2:$C$9
                        ,0
                       )
                )
         ) <> C2:C10
      ) *
      ( D2:D10 = "Active" )
     )

What's happening?

The IF condition makes sure that the match finds something in Sheet2. It's attempting to match the ID columns. If it finds a match, it returns the value from the COST column in Sheet2. If it doesn't find a match, it returns the original cost from Sheet1. That result is compared with the original cost from the sheet. If they're different, the result is TRUE.

The second part of the product just compares the Status column to the word "Active". The product multiplies TRUE or FALSE together. If both are true, you get 1. If not, you get 0.

The SUM will then count all the ones, giving you a total of the changes.

There are probably other, less complicated ways to solve this problem, but this one works.

Good Luck!

Upvotes: 0

Related Questions