Formula not functioning

[Camel123]

Hi,

Would really appreciate input on how I can get my formula working.

Excel screen shot:
https://imgur.com/a/9JC8UhS

How I want the formula to function:

If "Group" in column B= 1000 or 2000, in this case B8 and B9,

then,

I want the formula to sum all costs in column C, except for the costs where B equals 1000 and 2000. In this case sum all costs except C8 and C9.

then,

I want the formula to multiply the summed amount by 10 % if B=1000, and by 20 % if B= 2000.

I tried the following formula but did not really work, please note I am an Excel-amateur:
=IF(OR(B8=1000;B8=2000);SUMIFS($C$2:$C$1000;B:B;"<>1000";B:B;"<>2000");"");IF(B8=1000;C8*0,1;"");IF(B8=2000;C8*0,2;"")

 

[magpie2000k]

* misread post

 

[Camel123]

Anyone who can help and give some input?

 

[magpie2000k]

Im no pro I was able to do it in two parts with helper column but ran out of time... If I get the chance I will try again today

Ie I pulled into first column of results the sum of the values of 1000 and 2000 in cell below and then did the percentage.. hope that makes sense

Sure one of the pros will be able to write thr whole thing.. I was experimenting with and after that but got side tracked

 

[Camel123]

Im no pro I was able to do it in two parts with helper column but ran out of time... If I get the chance I will try again today

Ie I pulled into first column of results the sum of the values of 1000 and 2000 in cell below and then did the percentage.. hope that makes sense

Sure one of the pros will be able to write thr whole thing.. I was experimenting with and after that but got side tracked

Thanks for trying. I still have not managed to get it working :/

 

[magpie2000k]

Hi,

Would really appreciate input on how I can get my formula working.

Excel screen shot:
https://imgur.com/a/9JC8UhS

How I want the formula to function:

If "Group" in column B= 1000 or 2000, in this case B8 and B9,

then,

I want the formula to sum all costs in column C, except for the costs where B equals 1000 and 2000. In this case sum all costs except C8 and C9.

then,

I want the formula to multiply the summed amount by 10 % if B=1000, and by 20 % if B= 2000.

I tried the following formula but did not really work, please note I am an Excel-amateur:
=IF(OR(B8=1000;B8=2000);SUMIFS($C$2:$C$1000;B:B;"<>1000";B:B;"<>2000");"");IF(B8=1000;C8*0,1;"");IF(B8=2000;C8*0,2;"")

I have re read your post and I am confused.

I have put this =SUMIFS(C2:C12,B2:B12,"<>1000",B2:B12,"<>2000") in cell c13 ie under th $100 figure in your image. Tis is summing the column c where B does not = 1000 or 2000

I dont get what you are saying by want to multiply the summed amount by 10% if 1000 and 20% if 2000 as in this example I would have to sum by both....


What am I missreading

 

[magpie2000k]

Ie is thsi what you mean

=SUM(SUMIFS(C2:C12,B2:B12,"<>1000",B2:B12,"<>2000")+SUMIFS(C2:C12,B2:B12,1000)*0.1)+SUMIFS(C2:C12,B2:B12,2000)*0.2

So summs the whole of column c where column b is neither 1000 or 2000 and adds 10% where column b is 1000 and 20% where column b is 2000


Hoep I am right I am trying/

 

[Camel123]

Ie is thsi what you mean

=SUM(SUMIFS(C2:C12,B2:B12,"<>1000",B2:B12,"<>2000")+SUMIFS(C2:C12,B2:B12,1000)*0.1)+SUMIFS(C2:C12,B2:B12,2000)*0.2

So summs the whole of column c where column b is neither 1000 or 2000 and adds 10% where column b is 1000 and 20% where column b is 2000


Hoep I am right I am trying/

I do really appreciate your input. I tried your formula but I get following error= #NAME ?

 

[jtakw]

Hi,

I believe this is what you want:

Book1
	B	C	D
1	Group	Cost	Results
2	1234	100
3	4556	100
4	6778	300
5	8990	200
6	9987	300
7	7654	500
8	1000	0	270
9	2000	0	540
10	4321	400
11	1357	700
12	9753	100
Sheet75

Cell Formulas
Range	Formula
D2	=IF(OR(B2={1000,2000}),SUMIFS(C$2:C$12,B$2:B$12,"<>1000",B$2:B$12,"<>2000")*IF(B2=1000,0.1,0.2),"")

D2 formula copied down.

 

[Camel123]

Hi,

I believe this is what you want:

	B	C	D
Group	Cost	Results

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]4556[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]6778[/TD]
[TD="align: right"]300[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]8990[/TD]
[TD="align: right"]200[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]9987[/TD]
[TD="align: right"]300[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]7654[/TD]
[TD="align: right"]500[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]270[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]540[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]4321[/TD]
[TD="align: right"]400[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1357[/TD]
[TD="align: right"]700[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]9753[/TD]
[TD="align: right"]100[/TD]

</tbody>

Sheet75​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IF(OR(B2={1000,2000}),SUMIFS(C$2:C$12,B$2:B$12,"<>1000",B$2:B$12,"<>2000")*IF(B2=1000,0.1,0.2),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



D2 formula copied down.

Yes this seems to be what I am looking for, I will try it once I get back to work and let you know