Hi all,
Im trying to create a spreadsheet but my Excel's knowledge is honestly limited so I really would need the help of one you experts.
Basically what I have at the moment are multiple tabs, in which each tab is a different store, the important information are 3 columns, A with the dates, B with products and C with their availability.
Unfortunately I only have OpenOffice at home, so I cant really share a copy here (or if it would be useful anyway just let me know) but of course I have excel at my workplace.
Something like "Store 1" (the same goes for store 2 with same products basically):
Then I have a final tab in which I have a summary of all the requested products and their availability per store.
So I have for instance:
Im doing the above using the COUNTIF and COUNTIFS formulas (COUNTIF for the "Product X Requested" and COUNTIFS for the "Product X Available as of course it must check both column B and C).
I would like know to see the summary only for a specific month, lets say February so in the example above, the Store 1 should not count 1 Pear request which was also available but if I filter it, it still counts all the rows, visible and hidden. I read something about the SUBTOTAL formula but I do not know if it can be applied to replace both the COUNTIF and COUNTIFS in the different columns and as I said before, my knowledge is not so great.
Could you please help me out?
Thanks,
Marco
Im trying to create a spreadsheet but my Excel's knowledge is honestly limited so I really would need the help of one you experts.
Basically what I have at the moment are multiple tabs, in which each tab is a different store, the important information are 3 columns, A with the dates, B with products and C with their availability.
Unfortunately I only have OpenOffice at home, so I cant really share a copy here (or if it would be useful anyway just let me know) but of course I have excel at my workplace.
Something like "Store 1" (the same goes for store 2 with same products basically):
Dates | Requested products | Availability |
01/02/2020 | Pears | No |
01/02/2020 | Apples | Yes |
03/02/2020 | Pears | No |
03/02/2020 | Apples | No |
01/03/2020 | Pears | Yes |
Then I have a final tab in which I have a summary of all the requested products and their availability per store.
So I have for instance:
Apples requested | Apples Available | Pears Requested | Pears Available | |
Store 1 | 2 | 1 | 3 | 1 |
Store 2 | xx | xx | xx | xx |
Im doing the above using the COUNTIF and COUNTIFS formulas (COUNTIF for the "Product X Requested" and COUNTIFS for the "Product X Available as of course it must check both column B and C).
I would like know to see the summary only for a specific month, lets say February so in the example above, the Store 1 should not count 1 Pear request which was also available but if I filter it, it still counts all the rows, visible and hidden. I read something about the SUBTOTAL formula but I do not know if it can be applied to replace both the COUNTIF and COUNTIFS in the different columns and as I said before, my knowledge is not so great.
Could you please help me out?
Thanks,
Marco