Google Spreadsheet to count in different sheets

Question:

( by tomtom328 )

Dear all, 

I like to create a formula for inventory count:

Column O (of many sheets) outlines the different types of inventory that i carry:   itemA, itemB, itemC, itemD, itemE

I need a formula that simply counts through all the sheets the different types of items that are being used:  

for ITEM.A count: =countIf('sheet1','sheet2','sheet3' !O:O,"itemA")

for ITEM.B count: =countIf('sheet1','sheet2','sheet3' !O:O,"itemB")

for ITEM.C count: =countIf('sheet1','sheet2','sheet3' !O:O,"itemC")

for ITEM.D count: =countIf('sheet1','sheet2','sheet3' !O:O,"itemD")

for ITEM.E count: =countIf('sheet1','sheet2','sheet3' !O:O,"itemE")

now, some of these items that I carry are in the inventory, but are not being used; how do i count the number of items that are not in use  & at the same time, (in the above formula) account or subtract out the ones not being used.

in column F, I would mark down the comments: NOTINUSE

How do i do count that shows the number of itemsA,B,C, D, E   not in use?

for ITEMA NOTINUSE COUNT =   ?

Thank you greatly in advance!

Solution:

Following are the screenshot of sheet1, sheet2, sheet3 containing data as mentioned in the question:

sheet1:

sheet2: 

sheet3:

Now to count the no. of "itemA" in sheet1, put the following formula in Cell B1 of sheet4:

=counta(iferror(filter('sheet1'!O:O;'sheet1'!O:O="itemA")))


To count the no. of "itemA" that is "NOTINUSE" in sheet1, put the following formula in Cell B2 of sheet4:

=counta(iferror(filter('sheet1'!O:O;'sheet1'!F:F="NOTINUSE";'sheet1'!O:O="itemA")))


To count the no. of "itemA" that is in use in sheet1, you can have it by B1-B2, or you can also use the following formula in Cell B3 of sheet4:

=counta(iferror(filter('sheet1'!O:O;'sheet1'!F:F<>"NOTINUSE";'sheet1'!O:O="itemA")))


Have a look at the screenshot of "sheet4":

Now to count the no. of "itemA" in all sheets, put the following formula in Cell B5 of sheet4:

=counta(iferror(filter('sheet1'!O:O;'sheet1'!O:O="itemA")))+counta(iferror(filter('sheet2'!O:O;'sheet2'!O:O="itemA")))+counta(iferror(filter('sheet3'!O:O;'sheet3'!O:O="itemA")))

To count the no. of "itemA" that is "NOTINUSE" in all sheets, put the following formula in Cell B6 of sheet4:

=counta(iferror(filter('sheet1'!O:O;'sheet1'!F:F="NOTINUSE";'sheet1'!O:O="itemA")))+counta(iferror(filter('sheet2'!O:O;'sheet2'!F:F="NOTINUSE";'sheet2'!O:O="itemA")))+counta(iferror(filter('sheet3'!O:O;'sheet3'!F:F="NOTINUSE";'sheet3'!O:O="itemA")))


To count the no. of "itemA" that is in use in all sheets, you can have it by B5-B6, or you can also use the following formula in Cell B7 of sheet4:

=counta(iferror(filter('sheet1'!O:O;'sheet1'!F:F<>"NOTINUSE";'sheet1'!O:O="itemA")))+counta(iferror(filter('sheet2'!O:O;'sheet2'!F:F<>"NOTINUSE";'sheet2'!O:O="itemA")))+counta(iferror(filter('sheet3'!O:O;'sheet3'!F:F<>"NOTINUSE";'sheet3'!O:O="itemA")))

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html

Thanks,

Kishan.

iGoogleDrive