IF statements to calculate count

1. Re: IF statements to calculate count

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))))

2. Re: IF statements to calculate count

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?

3. Re: IF statements to calculate count

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

4. Re: IF statements to calculate count

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.

5. Re: IF statements to calculate count

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

6. Re: IF statements to calculate count

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.

7. Re: IF statements to calculate count

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

8. Re: IF statements to calculate count

EFcolegerald.xlsb

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

9. Re: IF statements to calculate count

Great thank you for all your all

10. Re: IF statements to calculate count

You're welcome.

11. Re: IF statements to calculate count

Are you any good at creating charts?

+ Reply to Thread
Page 2 of 2 First 1 2

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1