1

I am trying to create a Google Spreadsheet which is able to count the number of items, and also be sustainable when I add new items which won't affect the previous result.

The table at the left is the data, and on the right is the result I am looking for Example: Example

The two items in RED box are newly added, and the yellow cells show the results expected.

Basically, when there is a item is bigger than ZERO, I want it to be counted. However, I don't want the previous result to change when I drag the function across.

I tried to use the combination of =counta and vlookup, but it is not working properly.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Martin Li
  • 73
  • 1
  • 4

1 Answers1

0

Assuming your red box is around B8:E9 then in H3 copied across and down to suit: 

=COUNTIFS(C:C,"<>"&0,$B:$B,$G3)

will give the results you show (other than the highlighting). However, two more days of data and one table may get in the way of the other. You might want to relocate one table above the other so each has room to expand - or move one to another sheet which would then require sheet references to be added to the above formula.

I am not clear what issues you have had with whatever combination of =counta and vlookup you have tried.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • 1
    Thank you very much pnuts. This one solved my problem. This is just an example to represent the work I am doing. So don't worry about the locate of the table. Thank you very much again – Martin Li Nov 19 '15 at 13:46