0

Assume I have the following table setup:

    Date             Product Type        Supplier
   12-May-2015           C                XX
   23-Sep-2015           B                XK
   12-May 2015           C                XA

How can I count the number of times supplier XX shows up for product C in May by using no macros or VBA?

My logic would be something like: 

IF Product Type = C and Supplier = XX THEN and Month = May, THEN count.

But I am stumped on what functions to use as I understand there is no looping function in Excel without macros. I can't even use COUNTIF because I need to check IF statements first.

pnuts
  • 58,317
  • 11
  • 87
  • 139
noname738
  • 61
  • 6

3 Answers3

2

If you put the dates that span the month in which you are interested into I1 (earlier) and J1 (later), the Product in K1 and Suppplier in L1 then (as @findwindow has suggested): 

=COUNTIFS(A:A,">"&I1,A:A,"<"&J1,B:B,K1,C:C,L1)

Assuming Date is in ColumnA.

pnuts
  • 58,317
  • 11
  • 87
  • 139
2

You could do this with an Array Formula.

Type this into an empty cell:

=SUM(IF(MONTH(A2:A4)=5,1,0) * IF(B2:B4="C",1,0) * IF(C2:C4="XX",1,0))

After entering the formula, hold CONTROL+SHIFT+ENTER to indicate that it is an array formula.

Brian Pressler
  • 6,653
  • 2
  • 19
  • 40
  • Works perfect. Might I ask why making it an array took away my error which said that value was of the wrong data type (when using the exact formula you provided)? – noname738 Nov 23 '15 at 22:37
  • I'm not sure... I can't reproduce the error message you are seeing. – Brian Pressler Nov 24 '15 at 16:25
0

You could use SUMPRODUCT

=SUMPRODUCT(--(TEXT(A2:A20,"mmm")="May"),--(B2:B20="C"),--(C2:C20="XX"))
Bob Phillips
  • 437
  • 1
  • 3
  • 7
  • The value of this answer would be greater if you could provide an explanation of how the mechanics of using SUMPRODUCT like this works; it would not be intuitive for those who have never seen this method before. – Grade 'Eh' Bacon Nov 19 '15 at 15:28