Search code examples
google-sheetsgoogle-sheets-formula

How to list empty count in query

On the sheet "Telefon" I calculate values to generate invoices from.

To have an overview of the calculated values, there are querys in E6, H6 and K6. Unfortunately, I can't manage to count the blank values so that the values are displayed in the correct row.

enter image description here

enter image description here

i.e. the values in H7:J7 should be in H14:J14

This is the query I use in H6:

=QUERY(Archiv!A1:AA,"
select count(R), sum(U)
where N contains 'person2' and W contains '"&$C2&"'
group by V
limit 12
label count(R) 'Tage', sum(U) 'Anzahl'
",1)

Can someone help?

Solution

  • You may try this in Cell_H6:

    =map(B7:B18,lambda(Σ,hstack(
countifs(Archiv!R:R,"<>",Archiv!V:V,Σ,Archiv!N:N,"Person2",index(year(Archiv!W:W)),C2),
sumifs(Archiv!U:U,Archiv!V:V,Σ,Archiv!N:N,"Person2",index(year(Archiv!W:W)),C2))))