Multiple If statemnets

[Sam Harman]

Hi, I have a table where i have five cells with an entry between 0 and
100.

For example

A1 has the value of 20
A2 has the value of 30
A3 has the value of 40
A4 has the value of 8
A5 has the value of 89

What I would like to do in cell A6 is add up the cells A1:A5 as
follows:

If the cell value is 9.99 or less it hsould be counted as 0
If the cell value is between 10 and 32.99 it should be counted as 1
If the cell value is 33 and above it should be counted as 2

Therefore in the example above the sum would be 1 + 1 + 2 + 0 + 2 and
the result in Cell A6 would be 6

Is this possible ?

Thanks in advance

Sam

 

[Claus Busch]

Hi Sam,

Am Wed, 03 Aug 2011 17:53:17 +0100 schrieb Sam Harman:

If the cell value is 9.99 or less it hsould be counted as 0
If the cell value is between 10 and 32.99 it should be counted as 1
If the cell value is 33 and above it should be counted as 2

try:
=SUMPRODUCT(--(A1:A5>=10),--(A1:A5<33))+SUMPRODUCT(--(A1:A5>=33))*2


Regards
Claus Busch

 

[joeu2004]

A1 has the value of 20
A2 has the value of 30
A3 has the value of 40
A4 has the value of 8
A5 has the value of 89
What I would like to do in cell A6 is add up the cells A1:A5 as
follows:
If the cell value is 9.99 or less it hsould be counted as 0
If the cell value is between 10 and 32.99 it should be counted as 1
If the cell value is 33 and above it should be counted as 2

=SUMPRODUCT((A1:A100>=10)+(A1:A100>=33))

 

[Sam Harman]

=SUMPRODUCT(--(A1:A5>=10),--(A1:A5<33))+SUMPRODUCT(--(A1:A5>=33))*2

Hi Claus and thank you so much for such a quick reply.

Can I ask if the cells to be counted were not consecutive, for example
A1, A5, A9 etc could this formula still work and if so, how would I
compile it

Thanks again

Sam

 

[Claus Busch]

Hi Sam,

Am Wed, 03 Aug 2011 23:06:29 +0100 schrieb Sam Harman:

Can I ask if the cells to be counted were not consecutive, for example
A1, A5, A9 etc could this formula still work and if so, how would I
compile it

if the cells between are empty
=SUMPRODUCT((A1:A9>=10)+(A1:A9>=33))
will work
If the cells between have text
=SUMPRODUCT(--(A1:A9>=10),--(ISNUMBER(A1:A9)))+SUMPRODUCT(--(A1:A9>=33),--(ISNUMBER(A1:A9)))


Regards
Claus Busch

 

[joeu2004]

Am Wed, 03 Aug 2011 23:06:29 +0100 schrieb Sam Harman:

if the cells between are empty
=SUMPRODUCT((A1:A9>=10)+(A1:A9>=33))

Hmm, now where have I seen that formula before? ;-)

If the cells between have text
=SUMPRODUCT(--(A1:A9>=10),--(ISNUMBER(A1:A9)))
+SUMPRODUCT(--(A1:A9>=33),--(ISNUMBER(A1:A9)))

Or simply:

=SUMPRODUCT(ISNUMBER(A1:A9)*((A1:A9>=10)+(A1:A9>=33)))

However, that formula does not work if the cells in between contain numbers
that are not to be considered.

In that case, the question is: is there some criteria that distinguishes
the "relevant" numeric cells?

In your example, they are every 4th cell after A1. Is that right criterion?

 

[joeu2004]

PS...

As I mentioned previously, if the intervening cells (A2, A3, A4 etc) contain
numbers that you want to ignore, the solution depends on your criteria for
selecting the relevant cells.

In your example, they are every 4th cell (A1, A5, A9 etc). If that's the
criterion, then try [*]:

=SUMPRODUCT((MOD(ROW($1:$9)-1,4)=0)*((A1:A9>=10)+(A1:A9>=33)))

 

[Sam Harman]

PS...

As I mentioned previously, if the intervening cells (A2, A3, A4 etc) contain
numbers that you want to ignore, the solution depends on your criteria for
selecting the relevant cells.

In your example, they are every 4th cell (A1, A5, A9 etc). If that's the
criterion, then try [*]:

=SUMPRODUCT((MOD(ROW($1:$9)-1,4)=0)*((A1:A9>=10)+(A1:A9>=33)))

Hi and thanks for all the help here.....

I may not have used the best example for this but the desired outcome
if the same...


I have reproduced below the spreadsheet I am working with

AD2 AE2 AF2 AG2 AH2 AI2 AJ2 etc
399 60 15 78 9 12 19 4 21 41
5 12 53 9 17


What I am trying to do is look in cell AF2, AI2, AL2, AO2, and AR2 and
then by using the formula above arrive at a sum in cell AT2...

I hope that makes sense

Thanks again

Sam

 

[joeu2004]

I have reproduced below the spreadsheet I am working with

AD2 AE2 AF2 AG2 AH2 AI2 AJ2 etc
399 60 15 78 9 12 19 4 21 41
5 12 53 9 17

What I am trying to do is look in cell AF2, AI2, AL2, AO2,
and AR2 and then by using the formula above arrive at a sum
in cell AT2...

So, it looks like every 3rd column in a row. You might try:

=SUMPRODUCT(--(MOD(COLUMN($A:$O),3)=0),$AD2:$AR2)

I chose COLUMN($A:$O) because that results in the __row__ array
{1,2,...,14,15}, recognizing the fact that COLUMNS(AD2:AR2) is 15.

I would prefer to write ROW($1:$15). But that results in a __column__
array, and SUMPRODUCT does not like the mix of column and row parameters.

I thought the following should work:

=SUMPRODUCT(--(MOD(ROW($1:$15),3)=0),TRANSPOSE($AD2:$AR2))

But that only works if it is entered as an array formula [*]. You can do
that, if you prefer.

Note: I write $AD2:$AR2 so that you can copy the formula down the column,
if you wish. If you only need the one formula, you can write simply
AD2:AR2.

-----
[*] Enter an array formula by pressing ctrl+shift+Enter instead of Enter.
Excel will display an array formula surrounded by curly braces in the
Formula Bar, i.e. {=formula}. You cannot type the curly braces yourself.
If you make a mistake, select the cell, press F2 and edit, then press
ctrl+shift+Enter.

 

[joeu2004]

PS....

I thought the following should work:
=SUMPRODUCT(--(MOD(ROW($1:$15),3)=0),TRANSPOSE($AD2:$AR2))

But that only works if it is entered as an array formula [*].
You can do that, if you prefer.

If you are willing to use an array formula like that, you might as well
write the following:

=SUM(IF(MOD(ROW($1:$15),3)=0,TRANSPOSE($AD2:$AR2)))

I don't know if it is more or less efficient that the SUMPRODUCT form. But
it might be more intuitive.