IF statements to calculate count

Hello

I need some help on getting a count function working using sum product.

What I want is If I select a quarterly month in the list box on 'quarterly reports', I want it to count the number of inspections as shown on the 'inspections' data sheet for those 3 months.

I have tried to use this formula but not picking up the months

=SUMPRODUCT((TEXT(Inspections!$A$2:$A$5,"mmm")=$A$4)*(Inspections!E$2:E$5>=6)*(Inspections!E$2:E$5<=7))

The values in Inspections column A are actual real dates.
But A4 is just a text string "Jul Aug Sep"
So there are no matches.

Try
=SUMPRODUCT((ISNUMBER(SEARCH(TEXT(Inspections!A$2:A$5,"mmm"),$A$4)))*(Inspections!E$2:E$5>=6)*(Inspections!E$2:E$5<=7))

this is not working.

result for the first table when oct, nov, dec is selected should be 2 and not 0.

That is working now

if i wanted to select just the quarterly and only where it counts the east side for each category, do you know how that would go?

If you look at the quarterly datasheet and look at the 2nd table 'quartely inspections in the east', currently this counts the inspections based on the month selected against the feature and the grade. What I also want it to do is only count the inspections that show 'east' from the site group in the inspections datasheet

Sorry, I still can't see what you want.
Can you speak in exact Sheet Names, column Letters and row #s etc ?

I don't see anything that says 'quartely inspections in the east'
I don't see a 2nd table on any sheet..

Specifically..
I want to count the cells between rows ? and ?, in column ? on sheet ? that meet criteria ?

In the spreadsheet in the quarterly reports you have 3 tables.

In the 2nd table I want it to count the number of inspections against per grade in the east site group against the month that has been selected.

Attachment 423637

Please attach EXCEL workbooks, not word documents with pictures.
We can't work with those.

And I still don't see that 2nd table in the Excel Workbook you attached in post #1

Attachment 423638


see attached

So column B on the Inspections sheet is the 'Group'
And you want to filter only the 'East Side Group'

But there are no values in that column called 'East Side'
You have 'Rural East', is that what you want included? Anything that has EAST in the text of column B ?

Would it be better if you had just 1 table on the Quarterly Reports Sheet.
But you could Filter column B on the Instpections sheet for a particular group and the table on the Quarterly Reports sheet would update accordingly?

yeah I want it to pick up in that column anything that contains the word 'East' in the group and count and updated the table accordingly in the 'quarterly reports datasheet

the reason I have the 3 tables on the 'quarterly report datasheet' is because first data counts everything for those months.

Table 2- will count just the east

Table 3- will count just for the west

Originally Posted by colegerald38

the reason I have the 3 tables on the 'quarterly report datasheet' is because first data counts everything for those months.

I get that, what I'm asking is if it would be better for you if you had just 1 table.
But, when you go the the Inspections sheet and Filter column B for the particular group you want to see, that 1 table automatically adjusts to that?
It would be better as far as calculation speed goes.
Having 3 tables doing essentially the same work means 3 times the time needed to do the calculations.

Anyway, I get what you're after now, give me a few minutes.

This will add the criteria of column B containing the word East

=SUMPRODUCT((ISNUMBER(SEARCH(TEXT(Inspections!A$2:A$5,"mmm"),$A$14)))*(Inspections!E$2:E$5>=6)*(Inspections!E$2:E$5<=7)*(ISNUMBER(SEARCH("East",Inspections!$B$2:$B$5))))

thanks

If wanted to go with the one table, are you able to do the count for the west, east and the whole district where it is displayed separately?

I had set it up at 3 purely so it is easier to see the displayed information

Here's how you can do it with just 1 table.

I added a helper column to the inspections sheet column AJ
In AJ2 and filled down
=SUBTOTAL(103,[@[Sample Date]])
It returns a 1 if the row is visible, 0 if it's hidden.
You can hide that column.

Then the basic formula in the Quarterly Reports sheet, B5 is
=SUMPRODUCT((ISNUMBER(SEARCH(TEXT(Inspections!$A$2:$A$5,"mmm"),$A$4)))*(Inspections!E$2:E$5>=6)*(Inspections!E$2:E$5<=7)*Inspections!$AJ$2:$AJ$5)

When column B on the Inspecitons sheet is NOT filtered at all, everything shows in the table.
When you filter column B on the Inspecitons sheet and select only the group(s) you want to see, the table adjusts accordingly.

are you able to send me the excel worksheet with the formula inputted?

It's too big, I don't know how you managed to zip it to less than 1MB
But I can't, it's still over 3MB when I zip it.

i just saved it and then right clicked and selected 'send to compressed zip folder'

EFcolegerald.xlsb

I deleted all the hidden sheets, and removed macros
That cut the size way down.

Great thank you for all your all

You're welcome.

Are you any good at creating charts?