I have a table with values 1 and 2 in column B and I wish to know what the
results in columns B to X are based on this i.e If column A equals 1 and
column B equals 1 then I want to return a count of 1. And so on: if column A
equals 1 and column B equals 2 then return a count of 1. Can I also do this
for ranges if column C equals 10-20 and column B equals 1 then return a count
of 1. I have tried SUMIF, AND and IF in the Help but can't understand how to
the above?

Thanks for the help.

try this (for data in rows 1:100) (with assumed result of zero fo no
occurances)

=if(sumproduct((a1:a100=1)*(b1:b100=2))0,1,0)

=if(sumproduct((c1:c100=10)*(c1:c100<=20)*(b1:b10 0=1))0,1,0)

"docnige" wrote:

I have a table with values 1 and 2 in column B and I wish to know what the
results in columns B to X are based on this i.e If column A equals 1 and
column B equals 1 then I want to return a count of 1. And so on: if column A
equals 1 and column B equals 2 then return a count of 1. Can I also do this
for ranges if column C equals 10-20 and column B equals 1 then return a count
of 1. I have tried SUMIF, AND and IF in the Help but can't understand how to
the above?

Thanks for the help.

That is a start but it only returns the value 1, I want it to add all
occurences in the table where column B for example equals 1 and column F
equals 1 etc. So

Column B Column F
1 1
1 2
1 1
2 1

In this example the formula would return 2 for the above example.

I think this makes it clearer.


"duane" wrote:

try this (for data in rows 1:100) (with assumed result of zero fo no
occurances)

=if(sumproduct((a1:a100=1)*(b1:b100=2))0,1,0)

=if(sumproduct((c1:c100=10)*(c1:c100<=20)*(b1:b10 0=1))0,1,0)

"docnige" wrote:

I have a table with values 1 and 2 in column B and I wish to know what the
results in columns B to X are based on this i.e If column A equals 1 and
column B equals 1 then I want to return a count of 1. And so on: if column A
equals 1 and column B equals 2 then return a count of 1. Can I also do this
for ranges if column C equals 10-20 and column B equals 1 then return a count
of 1. I have tried SUMIF, AND and IF in the Help but can't understand how to
the above?

Thanks for the help.

try thi 9for rows 1:100 - adjust row range needed

=sumproduct((a1:a100=1)*(b1:b100=2))

adds occurances for 1 in column a and 2 in column b

=sumproduct((c1:c100=10)*(c1:c100<=20)*(b1:b100=1 ))

adds occurances for 10-20 in column c and 1 in column b


"docnige" wrote:

That is a start but it only returns the value 1, I want it to add all
occurences in the table where column B for example equals 1 and column F
equals 1 etc. So

Column B Column F
1 1
1 2
1 1
2 1

In this example the formula would return 2 for the above example.

I think this makes it clearer.


"duane" wrote:

try this (for data in rows 1:100) (with assumed result of zero fo no
occurances)

=if(sumproduct((a1:a100=1)*(b1:b100=2))0,1,0)

=if(sumproduct((c1:c100=10)*(c1:c100<=20)*(b1:b10 0=1))0,1,0)

"docnige" wrote:

I have a table with values 1 and 2 in column B and I wish to know what the
results in columns B to X are based on this i.e If column A equals 1 and
column B equals 1 then I want to return a count of 1. And so on: if column A
equals 1 and column B equals 2 then return a count of 1. Can I also do this
for ranges if column C equals 10-20 and column B equals 1 then return a count
of 1. I have tried SUMIF, AND and IF in the Help but can't understand how to
the above?

Thanks for the help.

Excellent, I think that works but to be sure I need to include the option of
a blank cell in column A as well so if column A is blank and column F is 1
then I need a formula for those occurences too?

"duane" wrote:

try thi 9for rows 1:100 - adjust row range needed

=sumproduct((a1:a100=1)*(b1:b100=2))

adds occurances for 1 in column a and 2 in column b

=sumproduct((c1:c100=10)*(c1:c100<=20)*(b1:b100=1 ))

adds occurances for 10-20 in column c and 1 in column b


"docnige" wrote:

That is a start but it only returns the value 1, I want it to add all
occurences in the table where column B for example equals 1 and column F
equals 1 etc. So

Column B Column F
1 1
1 2
1 1
2 1

In this example the formula would return 2 for the above example.

I think this makes it clearer.


"duane" wrote:

try this (for data in rows 1:100) (with assumed result of zero fo no
occurances)

=if(sumproduct((a1:a100=1)*(b1:b100=2))0,1,0)

=if(sumproduct((c1:c100=10)*(c1:c100<=20)*(b1:b10 0=1))0,1,0)

"docnige" wrote:

I have a table with values 1 and 2 in column B and I wish to know what the
results in columns B to X are based on this i.e If column A equals 1 and
column B equals 1 then I want to return a count of 1. And so on: if column A
equals 1 and column B equals 2 then return a count of 1. Can I also do this
for ranges if column C equals 10-20 and column B equals 1 then return a count
of 1. I have tried SUMIF, AND and IF in the Help but can't understand how to
the above?

Thanks for the help.