Replies

Your sample data is in A1:E5 including the labels.

{0,”Jan”,”Feb”,”Mar”,”prices”;
“Green”,”a”,”b”,”d”,26;
“Blue”,”v”,”c”,0,25;
“Brown”,”c”,”a”,”d”,150;
0,0,0,”d”,260}

where a 0 indicates an empty cell.

In B6 enter: =SUMPRODUCT((LEN(B2:B5)>0)+0,prices) [ where ‘prices” is the name of the range E2:E5]

Copy this formula across as far as needed.

Lets say that ImportData starts in A10 (the cell of the first numeric entry, thus not the cell of the label), use:

=DSUM(OFFSET(A10,0,0,MATCH(9.99999999999999E+307,A:A),1),$A$7,I10:J11)

The rule of applying COUNTIF when we have a between condition may appear confusing. It just requires some exercise. As I tried to state in my first reply, a multiconditional count or sum is ordinarily computed by means of an array formula or a SUMPRODUCT formula that is capable of operating on arrays (By the way, PivotTables etc can also be used). As a side note, array formulas are more generic than the SUMPRODUCT formulas. The former requires the key combination control+shift+enter, while the latter is entered as an ordinary formula.

COUNTIF and SUMIF, by design, cannot work with more than one condition/criteria. With a “between” condition (which boils down to multiple conditions), COUNTIF can still be used for counting (and SUMIF for summing/totaling).

Now back to your question.

Ambiguity of between (as Legare also noted):

Lets say that E1 houses the lowest and E2 the highest of criteria.

What do we mean when we say “between E1 and E2″? It can be one of the following:

> E1 and = E1 and E1 and = E1 and =”&E1)-COUNTIF(A:A,”>”&E2)

Note. Column A should not have anything else (any other numbers) but the numbers of interest that we want count.

How this formula works?

The first COUNTIF counts every cell in A that is greater than or equal to the value in E1. The second COUNTIF counts every cell in A that is greater than the value in E2. The latter count is then subtracted from the former count, producing the desired result.

The array and SUMPRODUCT versions are — these formulas by design won’t accept whole columns as range specifiers, so exact ranges must be provided:

{=SUM((A1:A34>=E1)*(A1:A34=E1)*(A1:A34<=E2)) [ normally entered ]

Aladin

Lets say that column A houses the numbers of interest, E1 the lowest criterion (your .75), and E2 the highest criterion (your 1).

=COUNTIF(A:A,”>”&E1)-COUNTIF(A:A,”>=”&E2)

will give you a count of numbers in A which are > .75 and < 1, thus excluding the numbers that are equal to the criteria. A bit tweaking will allow you to modify the formula for counting that is based on a different form of between.

This formula is "cheaper" than an equivalent array or SUMPRODUCT formula which are definitely needed when you want a multiconditional count or sum.

Aladin

John —

I think there are 2 possible readings of what the original poster wanted:

One reading implicates

(a) ORing and another
( ANDing.

I’m sure Jan Karel was also aware of this. He simply opted for reading (a).

In array or sumproduct formulas + does the ORing, * the ANDing. Your first attempt was altogether a different thing (which prompted my reply). Fred gave an array formula with IFs, equivalent to Jan Karel’s which is constructed with Boolean terms. If the latter reading would be what the OP wants, then

{=SUM((A1:A20<0)*(B1:B20<0))

would be indeed the answer.

By the way, these two formulas have the following, normally entered, equivalents with sumproduct:

=SUMPRODUCT((((A1:A10<0)+(B1:B100)+0)

=SUMPRODUCT((A1:A20<0)*(B1:B20<0))

Regards,

Aladin

Mike —

Try:

=SUMIF(SALESRECORD!C25:C40,TEXT(B3,”00000″),SALESRECORD!D25:D40)

the result of which proves that SALESRECORD!C25:C40 is text-formatted.

PS. Enter, say, in G1 in the worksheet SALESRECORD

=ISNUMBER(C25)

It gives 0, meaning FALSE. Why is it that I get 0 or 1 with this function in this ws instead of FALSE or TRUE?

Aladin

I don’t think your version

{=SUM(((D5:D14+E5:E14)<0)*1)}

does work the same as thart of Jan Karel, which is:

{=SUM((((A1:A10<0)+(B1:B100)*1)}

Their logical structure is different,

Try both on the following sample:

{-1,1;
1,1;
-2,-4;
-4,3;
-3,4}

Aladin

Given the structure of your examples,

{“ABC1234567”;
“AB12345678”;
“ABC12 34 567″}

which, say, A1:A3 houses,

in B1 enter: =SUBSTITUTE(A1,RIGHT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{” “,0,1,2,3,4,5,6,7,8,9},””))))),””)

in C1 enter: =SUBSTITUTE(A1,B1,””) or, depending on what you’d prefer, =SUBSTITUTE(SUBSTITUTE(A1,B1,””),” “,””)+0

Select B1:C1 and copy down.

Custom format the date cells as

dddd, mmmm dd, yyyy

The question involves totaling amounts involving a criterion month.

Lets say that A2:A100 houses full date entries and B2:B100 dollar amounts.

In order to use the SUMIF effectively, which is an appropriate function for the task, you need the following:

In C2 enter: =MONTH(A2) [or =TEXT(A2,”MMM”) ]

Copy down this formula till C100.

In D2 enter: a month number of interest if column C created with =MONTH(..) or a 3-letter month name if column C is created with =TEXT(…,”MMM”),

In E2 enter: =SUMIF($C$2:$C$100,D2,$B$2:$B$100)

Another method, where you don’t need to create column C, would be with SUMPRODUCT.

If D2 is a month number (the criterion month),

in E2 enter: =SUMPRODUCT((MONTH($A$2:$A$200)=D2)*($B$2:$B$100))

Or, if D2 is a 3-letter month name (the criterion month),

in E2 enter: =SUMPRODUCT((TEXT($A$2:$A$200,”MMM”)=D2)*($B$2:$B$100))

The first method (with SUMIF) should be preferred above the second if the actual data range consists of more than 1000 rows.

John —

A more effiecient formula for the task would be:

=COUNTIF(Countries,M2)>0

where Countries names a range in a column (or in a row for that matters).

By the way, MATCH is not a bad choice as you seem to think. I’d rather prefer it to VLOOKUP in this case (for esthetical reasons):

So:

=ISNUMBER(MATCH(M2,Countries,0)

would do just as well.

Note. Both formulas intendedly return a logical value, either TRUE or FALSE.