Counting a cell if either OR value is met but not both.

[DIZWAR]

Hello,

I am gathering a large amount of data and from that data I need to extract the following:

One column has values ranging from 1 to 20 lets say A1:A500, and the 2nd column has the same values ranging from 1 to 20 (B1:B500).

What I need to do is check if say A1 or B1 is less than 11. If they are both less than 11 then it is not counted. If only one is less than 11 they it is counted. The formula then needs to do the same for all cells A2:B2, A3:B3 etc etc

Looking at the 2 columns below here is what I mean. A1 is 15 and B1 is 17 so neither count. A2 is 4 and B2 is 6 so again neither should count. A3 is 23 and A3 is 2. Only one of these is below 11 and thus would increment the count

Going through the entries below I would expect a value of 5 to be returned. Apologies for the drawn out explanation. Thanks for your help in advance.

[TABLE="width: 279"]
<tbody>[TR]
[TD]15
[/TD]
[TD]17
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]16
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]21
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]19
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

Welcome to the board..


Try
=SUMPRODUCT(--((A1:A500<11)+(B1:B500<11)=1))

 

[DIZWAR]

Hi jonmo1,

That formula returns a value of 0 instead of 5. definitely feeling out of my depth on this one!!

Thanks again for your help

 

[Jonmo1]

Works for me.

If it's returning 0, it means none of the rows met the criteria.
Likely cause is that your numbers are not really numbers.
They are "Numbers Stored As Text"

What does this formula return
=ISNUMBER(A1)
Change A1 to a cell that appears to meet the criteria.

Excel Workbook
	A	B	C	D
1	15	17		5
2	4	6
3	23	2
4	17	12
5	19	16
6	18	20
7	4	1
8	22	6
9	2	21
10	13	7
11	7	1
12	21	19
13	14	8
14	5	9
Sheet1

 

[DIZWAR]

Sorted. I had some other data in one of the columns frm when I have messing about with it that was throwing it off.

I also now need to reference these cells to another column e.g. If A1 and B1 meet the criteria. you must only increment the count if C1 ="W". Apologies for not including everything at the beginning I didn't expect such swift assistance.

 

[Jonmo1]

Glad to help

Try
=SUMPRODUCT(--((A1:A500<11)+(B1:B500<11)=1),--(C1:C500="W"))

 

[DIZWAR]

jonmo1,

Much obliged again. Last question (maybe). I now have a value of 4 qualifying cells. What I would like to do now is from these 4 qualifying cells is reference another column that has a monetry value in and add them 4 together. Hope this makes sense.