# IF statements to calculate count

1. ## 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))

2. ## Re: IF statements to calculate count

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

3. ## Re: IF statements to calculate count

this is not working.

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

4. ## Re: IF statements to calculate count

That is working now

5. ## Re: IF statements to calculate count

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?

6. ## Re: IF statements to calculate count

Looks like you posted again while I was responding.
So the formula did work.

Not sure I understand what you're asking now about 'east side' ?

7. ## Re: IF statements to calculate count

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

8. ## Re: IF statements to calculate count

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 ?

9. ## Re: IF statements to calculate count

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

10. ## Re: IF statements to calculate count

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

11. ## Re: IF statements to calculate count

Attachment 423638

see attached

12. ## Re: IF statements to calculate count

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?

13. ## Re: IF statements to calculate count

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

14. ## Re: IF statements to calculate count

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

15. ## Re: IF statements to calculate count

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.

Page 1 of 2 1 2 Last

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