0

I am trying to develop a query which should pick out and count the data from a data pool corresponding to the year/month in the given (A106) cell.

While such (array) construct works:

COUNTIF(IF(MONTH(INDEX(Data.$A$1463:$A$1827))=MONTH(A106);Data.$B$1463:$B$1827);">0");

such one - does not:

COUNTIF(IF( MONTH(INDEX(Data.$A$1463:$A$1827))=MONTH(A106) and  YEAR(INDEX(Data.$A$1463:$A$1827))=YEAR(A106));Data.$B$1463:$B$1827);">0")

It there anything to be done about it or it is impossible?

Andrew Schwartz
  • 4,440
  • 3
  • 25
  • 58
JanisE
  • 37
  • 1
  • 8

1 Answers1

0

I believe that you might want to use a sumproduct for this (though there are certainly other ways to achieve the same result). This might do the trick:

SUMPRODUCT((MONTH(INDEX(Data.$A$1463:$A$1827))=MONTH($A$106))*(YEAR(INDEX(Data.$A$1463:$A$1827))=YEAR($A$106)))
MikeG
  • 85
  • 8
  • "SUMPRODUCT: Multiplies corresponding elements in the given arrays, and returns the sum of those products." I do not catch - what is the reason to use sumproduct(), if I need to filter out and count _the number_ of specific occurances being greater than zero and having the same month and date? – JanisE May 06 '16 at 06:38
  • Sumproduct can be used in the same way as Countif, each of the checks for equality, like `(Month(Index(Data.A1463))=Month(A106))`, results in a TRUE or FALSE. When Booleans are applied in arithmetical operations (like multiplication) they automatically convert to 1 or 0. Thus, If the months are equal AND the years are equal, we end up with 1*1. Everything else produces a 0. Sumproduct then adds up all the 1's and 0's to tell you the number of times both conditions were met in the arrays. – MikeG May 06 '16 at 12:37
  • Another way to do it is `Sumproduct(--(Month(Index(Data.$A$1463:$A$1827))=MONTH($A$106)), --(YEAR(INDEX(Data.$A$1463:$A$1827))=YEAR($A$106)))` – MikeG May 06 '16 at 12:39
  • Yes, SUMPRODUCT correctly counts occurrences of the given month/year combinations, but I need one level deeper - i need to count values in the specific column greater than zero within selected month/year region. (see the original question's working example with the case of MONTH()) – JanisE May 06 '16 at 18:28
  • Lets put it other way - Data sheet contains a lot of data having date as the first column and variable combinations of specific data in other columns - I need to count the number of non-zero occurrences of specific parameter (let it be Data.$B$1463:$B$1827) within month/year specified in A106. – JanisE May 06 '16 at 18:36
  • `Sumproduct(--(Month(Index(Data.$A$1463:$A$1827))=MONTH($A$106)), --(YEAR(INDEX(Data.$A$1463:$A$1827))=YEAR($A$106)), --($B$1463:$B$1827>0))` – MikeG May 06 '16 at 18:42
  • Excellen! btw - what syntax "--" means? – JanisE May 08 '16 at 12:13
  • `--` is a way of forcing logical T/F values into 1/0 so that they can be summed. – MikeG May 08 '16 at 12:36
  • Is there the way how to calculate the sum of the some other field using the information acquired by Sumproduct? – JanisE May 18 '16 at 08:59