How to use 1 formula to Count items by date?-CodePudding

https://docs.google.com/spreadsheets/d/1_b-wdVQFKKSpJtNF4coNhQvmv20mUFSGkpppbOTE9Cg/edit?usp=sharing

Hi, I am trying to build a report. How can I write 1 formula at B2 to count the SKUs by date? Thank you so much for your help.

CodePudding user response：

See my added sheet ("Erik Help"). The formula in B2:

=ArrayFormula(IFERROR(VLOOKUP(FILTER(A2:A,A2:A<>"")&"~"&FILTER(B1:1,B1:1<>""),QUERY({Data!D2:D&"~"&Data!A2:A},"Select Col1, COUNT(Col1) GROUP BY Col1"),2,FALSE)))

The FILTERs aren't strictly necessary, but they will speed up processing, especially if you have a lot of data in your real sheet.

Every element from A2:A (the SKUs) is concatenated with a tilde ~ and then every element of B1:1 (the dates).

QUERY forms a two-column grouping of each SKU~date and COUNT of each from the Data sheet.

VLOOKUP then acts on every element of the virtual array grid, trying to find it within the QUERY. If found, the COUNT is returned. If not, IFERROR returns null.

CodePudding user response：

You can also try this formula in B2 and just drag it to the last column.

=ARRAYFORMULA(IF(ISBLANK($A2:$A)," ", (COUNTIFS(Data!$D:$D, $A2:$A,Data!$A:$A, B1))))