Filtering Countifs

marcocm4

New Member
Joined
Feb 14, 2020
Messages
4
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):
DatesRequested productsAvailability
01/02/2020PearsNo
01/02/2020ApplesYes
03/02/2020PearsNo
03/02/2020ApplesNo
01/03/2020PearsYes

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 requestedApples AvailablePears RequestedPears Available
Store 12131
Store 2xxxxxxxx

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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Book1
BCDEFGHIJK
2Apples requestedApples AvailablePears RequestedPears Available
3Store 12131
4
5DatesRequested productsAvailability
61/2/2020PearsNo
71/2/2020ApplesYes
83/2/2020PearsNo
93/2/2020ApplesNo
101/3/2020PearsYes
Sheet1
Cell Formulas
RangeFormula
H3H3=SUMPRODUCT(SUBTOTAL(3,OFFSET(C6:C10,ROW(C6:C10)-MIN(ROW(C6:C10)),,1)),ISNUMBER(SEARCH("Apple",C6:C10))+0)
I3I3=SUMPRODUCT(SUBTOTAL(3,OFFSET(D6:D10,ROW(D6:D10)-MIN(ROW(D6:D10)),,1)),ISNUMBER(SEARCH("Apple",C6:C10))*ISNUMBER(SEARCH("Yes",D6:D10)))
J3J3=SUMPRODUCT(SUBTOTAL(3,OFFSET(C6:C10,ROW(C6:C10)-MIN(ROW(C6:C10)),,1)),ISNUMBER(SEARCH("Pear",C6:C10))+0)
K3K3=SUMPRODUCT(SUBTOTAL(3,OFFSET(D6:D10,ROW(D6:D10)-MIN(ROW(D6:D10)),,1)),ISNUMBER(SEARCH("Pear",C6:C10))*ISNUMBER(SEARCH("Yes",D6:D10)))
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top