Help with functions in MS Excel

[geo40]

Hello,

I hope someone can help me with following issue in MS Excel.

I've following columns in our OFFER-database:

Cost-place Project Days outstanding
101 A 22
101 B 45
201 C 95
201 D 42
201 E 43
301 F 2

From above standing database I want to make the following ( column )division:

Piece of offers

Cost-place < 31 days =< 45 days > 45 days
101 1 1 0
201 0 2 1
301 1 0 0

Who can help ??

 

[onedtent]

assuming ur data is on sheet1, & u want the calc on sheet2,
if A2 on sheet 2 is 101, the formula in B2 would be
=SUM(IF((Sheet1!$A$2:$A$7=A2)*(Sheet1!$C$2:$C$7<31),1,0))
entered as an array formula. ie once u have type the formula press ctrl+shift+enter

formula in c2 would be
=SUM(IF((Sheet1!$A$2:$A$7=A2)*(Sheet1!$C$2:$C$7<=45),1,0))

and d2
=SUM(IF((Sheet1!$A$2:$A$7=A2)*(Sheet1!$C$2:$C$7>45),1,0))

 

[geo40]

Please can you be more specific ? When I entered your formula I got an error-message. I think this has to do with the &quot;1&quot; and &quot;0&quot; in your formula.

I also don't understand what you mean by &quot;enter as an array formula&quot;.

Thanks in advance.

 

[mymou]

Hi geo40

As an alternative - you could use the COUNTIF function. Then use TOOLS/SUBTOTALS.

I'm just not a big fan of arrays!

By the way your totals are wrong - are you sure you criteria is correct?
< 31 days     =< 45 days      > 45 days
1 2 0
0 2 1
1 1 0

Stew

 

[xlbo]

Hi:
&quot;. ie once u have type the formula press ctrl+shift+enter&quot;

The explanation is right there. Instead of just pressing ENTER when you have finished the formula, use CTRL+SHIFT+ENTER. You will see { } round your formula if you have donme it right to indicate that it is an array formula
Rgds
~Geoff~

 

[geo40]

To everyone who helped me I can say: Thank you very much.
In the formula I've replaced the &quot;,&quot; for a &quot;;&quot; and this works.

Indeed Mymou you were right because the totals aren't right. In one formula I need a criterium that says:

>= 31 days and <= 45 days outstanding.
Any suggestions ???????????

Regards GE04O

 

[mymou]

Hi GE04O

If your using the array function:
Replace
Sheet1!$C$2:$C$7<31
with
(AND(Sheet1!$C$2:$C$7>=31,Sheet1!$C$2:$C$7<=45))
I haven't tried it - but i see no reason why it shouldn't work.

Did you try out countif? If you dont use it here - still check if out and the subtotaling is even better.

Stew

 

[geo40]

Dear Mymou or others,

I tried following formula:

{=sum(if((Sheet1!$A$2:$A$7=A2)*(AND(Sheet1!$C$2:$C$7>=31,Sheet1!$C$2:$C$7<=45));1;0))}

I should get &quot;2&quot; as answer, but I get &quot;0&quot;. What is it that I do wrong ?

By the way: could you describe the &quot;Countif&quot; alternative ?

Gr.

GEO40

 

[mymou]

Hi geo40

You can check the help to get more info - but it is very easy to use:

=COUNTIF(Sheet1!$C$2:$C$7,&quot;<31&quot

Havent checked your above problem out yet.

Stew

 

[xlbo]

Try this instead:
{=sum((Sheet1!$A$2:$A$7=A2)*(Sheet1!$C$2:$C$7>=31)*(Sheet1!$C$2:$C$7<=45))}
(Still an array formula so needs CTRL + SHIFT + ENTER )

Unless there are N/As or the like in your data range, there is no real need to use IF statements in Array formulae as the result of an array formula is a set of 1s and 0s.
What happens is, each bracketed expression is tested and returns a Boolean result (true or false - True = 1, False = 0)
So, you end up with an array like this
=A2 >=31 <=45
1 0 0
0 0 0
1 1 1
1 1 0
1 1 1

Because you are summing the reults together, only a true answer to ALL tests will result in a 1 (anything * 0 = 0)
If you want to get the sum of all the answers, you just add a last expression covering the range you want to sum (which then gets multiplied by 1 for each row that is TRUE)
HTH Rgds
~Geoff~