COUNTIF with AND

[MartinL]

Hi Guys

I have a formula that I am having issues with, there is a an example file here
https://app.box.com/s/08cucpffqosyiswsi15c

The formula is
=SUMIFS($D$4:$D$99983,$A$4:$A$99983,AND("=$I5","<$I5"+1),$D$4:$D$99983,J$4)
I think the AND part is rubbish.

I5 in this cell = 1

Col D will be 1, 2, 3 or N/A
Col A is 1.1 ... 1.23, 2.1 ... 2.15 etc

hope this makes sense to someone.

Martin

 

[Aladin Akyurek]

What is column A - text or number? If number, what is exactly the conditions that must hold for this column? And what is in J4?

 

[Andrew Poulsom]

Is it?

=SUMIFS($D$4:$D$99983,$A$4:$A$99983,">="&$I5,$A$4:$A$99983,"<"&$I5+1,$D$4:$D$99983,J$4)

 

[MartinL]

Hi Aladin

Col A is Numeric
The conditions/values are 1.1 to 1.23 then 2.1 to 2.15 the 3.1 to 3.19 all the way up to 6.10

I need to allow for additional statment lines and for statement lines to be deleted, so for example statement 1.5 could be deleted or an additional statement could be added to section 3 ie 3.20

J4 is a table in this case J4 = Score of 1
K4 = Score 3
L4 = Score 4
M4 = Score N/A (not applicable)

I5 = section 1 (1.1 to less than 2)
I7 = section 2 (2.1 to less than 3)
I9 = section 3 (3.1 to less than 4)
I11 = section 4 (4.1 to less than 5)
I13 = section 5 (5.1 to less than 6)
I15 = section 6 (6.1 to less than 7)

Scores across the top (columns)
Sections in the rows
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[TD]

M​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]3
[/TD]
[TD]

N/A​

[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MartinL]

Thanks Andrew

Spot on, I can also see the logic.
any idea why the AND statement does work, just for information

Martin

 

[Andrew Poulsom]

You can't use AND in COUNTIFS. The criteria are just strings.