Sum IF problem

[Matt_Thompson]

Hi,

I'm really sorry if this is simple but it has had me stumpeed now for the best part of 2 hours.

I have a SUMIF formula =(SUMIF(STI!$C:$C,$C15&"NAT ACCS",STI!$S:$S).
The data in STI cannot be manipulated as it feeds several other spreadsheets but what I have been asked to do is subtract 2million from the total that the SUMIF returns.

I tried =((SUMIF(STI!$C:$C,$C15&"NAT ACCS",STI!$S:$S)-2) which sisn't work and I've tried a few other variations on this but nothing seems to work.

If anyone can help I'd be really greatful.

Thanks,

Matt

 

[Richard Schollar]

Matt

In the formula you posted you have two brackets after the equals sign ie:

=(( Sumif...

when in the given formula you only need 1 - is this just a typo? Your formula should work otherwise.

Best regards

Richard

 

[Matt_Thompson]

Sorry I was being stupid!

And also the spreadsheet shanged itself from auto calculate to manual calculate just to confuse me even more!

Thanks,

Matt

 

[erik.van.geit]

Hi, Matt,

your formula works for me (without some brackets)
=SUMIF(STI!$C:$C,$C15 & "NAT ACCS",STI!$S:$S)
with ThisText in C15
and this table
  C                D  S   
2 ThisTextNAT ACCS    1   
3 ThisTextNAT ACCS    2   
4 ThisText other      3   
5 ThisText            4   
6 other               5   
7 other               6   
8 other               7   
9 ThisTextNAT ACCS    100 

STI
[Table-It] version 05 by Erik Van Geit

the result would be 103

kind regards,
Erik

EDIT: seems like my post took its time to come trough