# Can't add criteria to SUM(COUNTIF(INDIRECT

1. ## Can't add criteria to SUM(COUNTIF(INDIRECT

Apologies if this has been covered before, but I can't find anything on the net?

Formula in AU3 and AV3 on the attached sets a "fixed " range of non-contiguous columns to calculate how many night shifts and how many early/afternoon shifts match a string over two weeks.

=SUM(COUNTIF(INDIRECT({"E4:K15","Q4:W15","AC4:AG15","AL4:AN15"}),"*SHELLS*"))

I want to add two criteria to the formula, so it finds only the records where the Trading Period in COl D matches AS4 and AS5, and the Weeks in Col C match AT4 and AT5

Col AX:BB show what the criteria should produce.

Any solutions, suggestions or alternative welcome as ever.

Ochimus

2. ## Re: Can't add criteria to SUM(COUNTIF(INDIRECT

If you can unmerge the header in row 1 and put the header like to

Early Early Early Afternoon Afternoon Afternoon

AU4
=SUMPRODUCT((ISNUMBER(SEARCH("SHELLS",\$E\$4:\$AQ\$15))*(\$E\$1:\$AQ\$1<>"Night")*(\$C\$4:\$C\$15=\$AT4)*(\$D\$4:\$D\$15=\$AS4)))

AV4
=SUMPRODUCT((ISNUMBER(SEARCH("SHELLS",\$E\$4:\$AQ\$15))*(\$E\$1:\$AQ\$1="Night")*(\$C\$4:\$C\$15=\$AT4)*(\$D\$4:\$D\$15=\$AS4)))

An easy way to fill the header
Select E1:AP1 > unmerge > Press F5 > Special > Blank > Key-in = and press left arrow > Press Ctrl+Enter

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