Replies

Ricky,

In C3 enter:

=B3+IF(1<WEEKDAY(B3),7-WEEKDAY(B3)+1,1-WEEKDAY(B3))

In C4 enter and copy down:

=C3+7

In D3 enter:

=DATE(YEAR(B3),MONTH(B3)+1,0) [ or: =EOMONTH(B3,0), which requires the Analysis Toolpak add-in ]

In D4 enter and copy down:

=IF(COUNTIF(B:B,D3+1),DATE(YEAR(D3+1),MONTH(D3+1)+1,0),"")

I created an additional sheet, named Admin, in order to define named dynamic ranges:

(a) DATES, which refers to:

=OFFSET(Dates!$B$1,0,0,DateRecs,1)

( SUNDAYS, which refers to:

=OFFSET(Dates!$C$1,0,0,SundayRecs,1)

© MONTHENDS, which refers to:

=OFFSET(Dates!$D$1,0,0,MonthEndRecs,1)

where DateRecs is computed in B1 in Admin with

=MATCH(9.99999999999999E+307,Dates!B:-(CELL("row",Dates!B3)-1)

SundayRecs in B2 with

=B1

and MonthEndRecs in B3 with

=MATCH(9.99999999999999E+307,Dates!D:D)-(CELL("row",Dates!D3)-1)

You can use SUNDAYS and MONTHENDS as source for the list boxes that you want to set up.

Aladin

Try:

=SUMPRODUCT((COUNTIF(B6:B12,”*w*”)))

or

=SUMPRODUCT((COUNTIF($B$6:$B$12,”*”&A1&”*”)))

where A1 houses just the target letter (e.g., “w” without double quotes)

An option is not to use the Conditional Sum Wizard. Instead, try to devise the required formulas yourself directly where they are needed.

=IF(COUNTIF($A$2:$A$1217,C2),”VALID”,”delete”)

should be a bit more efficient.

Also

=SUMPRODUCT(ISNUMBER(SEARCH(“200108”,A3:A34))*ISNUMBER(SEARCH(“Test”,B3:B34)))

will produce the desire count.

=(C20=”TOIL”)*(SUM(C9,C12,C15,C18))+(C20=”OTP”)*(SUM(P27,-C21))

Is this what you’re looking for?

Use:

=”‘”&MCONCAT(A1:A50,”‘,'”)&”‘”

MCONCAT is a function (among many others) available in Longre’s Morefunc add-in, which is downloadable from:

http://longre.free.fr/english/index.html%5B/url%5D

Use a different formula that returns #N/A instead of a 0 or “” like in:

=IF(A1,do-this,#N/A)

Aladin

a bit “eigenartig” (closest English translation = ornery)

I believe the translation is ‘peculiar’.

Aladin

Your “Team” column has not the same underlying format as your “Testing ADU” column.

You can see that by using in G19:

{=SUM(IF((ADadu=A18&””)*(ADvic=B19)*(ADstatus=G11),1,0))}

You can either re-create your “Team” column which you format as text before entering anything, or you use the above formula or its equivalent which you don’t need entering with control+shift+enter:

=SUMPRODUCT((ADadu=A18&””)*(ADvic=B19)*(ADstatus=G11))

After repairing the “Team” column, you can remove the &”” bit.

Aladin

I believe you confounded me with the original poster (OP).

It’s of course a good idea if that’s what the OP wants.

Aladin

If that’s what is wanted, a COUNTIF formula would suffice:

=IF(COUNTIF(A1:A13,A14),”Yes”,”No”)

or just

=COUNTIF(A1:A13,A4)

where the cell of the formula can be custom formatted as:

[=0]”No”;”Yes”

Aladin

It seems that you want to check whether a given entry occurs more than once in exactly the same shape. If so,

in A2 enter and copy down:

=SUMPRODUCT((EXACT($A$2:$A$10,A2))+0)

I’d suggest a different route, which avoids using array-formulas.

First create dynamic name ranges in theWorkbook2.xls reagrding the ranges in F and G.

theWorkbook2.xls:

Change the name Sheet1 to Data.

Activate Insert|Name|Define.
Enter NumRecs as name in the Names in Workbook box.
Enter as formula in the Refers to box.

=MATCH(9.99999999999999E+307,Data!$G:$G)

Activate Add. (Don’t leave yet the Define Name window.)

Enter DataRecs as name in the Names in Workbook box.
Enter as formula in the Refers to box.

=NumRecs-(ROW(Data!$G$5)-1)

Activate Add. (Don’t leave yet the Define Name window.)

Enter Grange as name in the Names in Workbook box.
Enter as formula in the Refers to box.

=OFFSET(Data!$G$5,0,0,DataRecs,1)

Activate Add. (Don’t leave yet the Define Name window.)

Enter Frange as name in the Names in Workbook box.
Enter as formula in the Refers to box.

=OFFSET(Data!$F$5,0,0,DataRecs,1)

Activate OK.

theWorkbook1.xls: (Yes, I mean workbook 1 where you have your original SUMIF formulas).

Rename the worksheet of the original SUMIF formulas as Summary.

In A from A2 on enter the conditions/criteria for which you want to compute the totals using data from
theWorkbook2.xls. The first condition to be entered:

MAT (in A2)
DAT (in A3)
etc.

Back to theWorkbook2.xls:

Insert a worksheet and rename it SumData.

In A2 enter and copy down as far as needed:

=’c:/[theWorkbook1.xls]Summary’!$A2

In B2 enter and copy down as far as needed:

=SUMIF(Frange,A2,Drange)

Back to Summary in theWorkbook1.xls:

In B2 enter and copy down as far as needed:

=’c:/[theWorkbook2.xls]SumData’!$B2

Now you can keep theWorkbook2.xls closed.

Aladin

In case of (formula-generated) blanks I’d suggest using:

=SUM(IF(LEN(A1:A5),1/COUNTIF(A1:A5,A1:A5)))

array-entered of course.

The particular use of COUNTIF is an invention of David Hager. Enforcing it with the LEN test originates from my efforts in fighting the formula-generated blanks.

Aladin