IF, sumif, and sumifs - how to calculate based on column criteria

[jex57]

Hi All,

Am wondering if someone can help me with a formula.

My data is more in columns than rows, so for example

Count1 Count 2 Count 3 Count4 Sum count
X 5 3 1

Name1 5 5 2 0 sum name1
Name2 a a 1 1 sum name 2


What I need to say in simple terms:

If there is an X in count column - sum all the counts EXCEPT the x - this obviously is a standard sumif statement

If there is an A in the name row - sum all the name EXCEPT where there is an a

If there is a X in a count column - regardless of what is in the name - do not add to my sum name total

So using this

My total for Count Columns should be 8
Total for name 1 should be 7 (the other 5 in the same column does not get included because it has an X)
Total for name 2 should be 2


My end result is to calculate percentages,
so for name 1 I would have 7 / 8 to get a mark
Name 2 I would have 2/4 to get a mark

Is anyone able to put this into a formula - I have tried many variations, and somehow I cant get the totals to work and need it to be done in cells.

Thank you

 

[mubashiraziz]

Do you need something like this ?

Excel 2012
	A	B
1	Name	count
2	a	x
3	a	5
4	b	3
5	b	3
6
7
8
9		6
Sheet9

Cell Formulas
Range	Formula
B9	=SUMIFS(B2:B5,A2:A5,"<>"&"a")

 

[jex57]

Problem is that is going down and not row totals and no x. I don't know how to do a screen shot to show but if you put the figures I posted in the message it might make more sense. Will try post a screen shot

 

[mubashiraziz]

Try to use some excel html editor developed by Mr. Excel. Or you can upload image here TinyUpload.com - best file hosting solution, with no limits, totaly free

 

[jex57]

Table looks something like this:
There are a number of tests that a child will write
In the second row:
This is the total that each test is out of.
X denotes that the test is not to be included in the year mark and therefore should not be used for calculations BUT I still need to be able to see what the child got
A denotes the child has been absent - that specific test needs to be completely excluded from the overall calculation.
Using Name1 as an example:
Child is absent for a test out of 15, therefore the childs mark is no longer calculated out of 35 but rather is worked out out of 20
Any ideas how to solve this?

	Test1	Test2	Test3	TOTAL	FORMULA
	X	20	15	sum all where <> X
NAME1	10	15	A	15 Sum all where <> X in row 2 and <> A in row 3	15 OUT OF 20 The x is excluded the A is excluded
NAME2	10	20	10	30	30 out of 35 The x is excluded there are no As
NAME3	A	10	10	20	20 out of 35 the X is excluded The a is in the x colum

<tbody>
</tbody>

 

[Aladin Akyurek]

E2, copied down:

=SUMIFS(B2:D2,$B$1:$D$1,"<>X")

 

[Aladin Akyurek]

Try to use some excel html editor developed by Mr. Excel. Or you can upload image here TinyUpload.com - best file hosting solution, with no limits, totaly free

No links to outside. Attachments lists a few methods for posting sample exhibits and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl
is also a method for posting an exhibit.

 

[mubashiraziz]

Table looks something like this:
There are a number of tests that a child will write
In the second row:
This is the total that each test is out of.
X denotes that the test is not to be included in the year mark and therefore should not be used for calculations BUT I still need to be able to see what the child got
A denotes the child has been absent - that specific test needs to be completely excluded from the overall calculation.
Using Name1 as an example:
Child is absent for a test out of 15, therefore the childs mark is no longer calculated out of 35 but rather is worked out out of 20
Any ideas how to solve this?

Try This


Excel 2012

	A	B	C	D	E
1		Test1	Test2	Test3	TOTAL
2		X	20	15
3	NAME1	10	15	A	15 Out of 20
4	NAME2	10	20	10	30 Out of 35
5	NAME3	A	10	10	20 Out of 35

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Tests​

Array Formulas Cell Formula E3 {=SUM(IF($B$2:$D$2<>"X",B3:D3))& " Out of "&SUM(IF(B3:D3<>"A",$B$2:$D$2))} E4 {=SUM(IF($B$2:$D$2<>"X",B4:D4))& " Out of "&SUM(IF(B4:D4<>"A",$B$2:$D$2))} E5 {=SUM(IF($B$2:$D$2<>"X",B5:D5))& " Out of "&SUM(IF(B5:D5<>"A",$B$2:$D$2))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[jex57]

Thank you - works.....

 

[Aladin Akyurek]

Thank you - works.....

What?