Sum/Count Multiple columns
 

Help!

I'm trying to get a formula that will work out the following, without
inserting any new columns on sheets I'm looking at.

I Need to Sum and count when

Column B = "Ongoing"
Column D = (This is variour different types of customer)

The column I need totalled is Column K.

It also needs counting so I can work out what the average order interval is.

I can get it to work if I add another column which uses B&D, together. But
Ideal I want a sep formula to work it out.

Any Ideas?

Sum/Count Multiple columns
 

Hi

Try something like this to SUM:
=SUMPRODUCT((B2:B1000="Ongoing")*(D2:D1000="Whatev er")*(K2:K1000))
and this to count:
=SUMPRODUCT((B2:B1000="Ongoing")*(D2:D1000="Whatev er"))

You cannot use full columns in SUMPRODUCT and the ranges must be the same
size.

Hope this helps.
Andy.

"Tom Hewitt" wrote in message
...
Help!

I'm trying to get a formula that will work out the following, without
inserting any new columns on sheets I'm looking at.

I Need to Sum and count when

Column B = "Ongoing"
Column D = (This is variour different types of customer)

The column I need totalled is Column K.

It also needs counting so I can work out what the average order interval
is.

I can get it to work if I add another column which uses B&D, together.
But
Ideal I want a sep formula to work it out.

Any Ideas?

Sum/Count Multiple columns
 

Brilliant!!

Works fantastic. Didn't realise you couldnt use full columns in sumproduct!

thanks for your help


"AndyB" wrote:

Hi

Try something like this to SUM:
=SUMPRODUCT((B2:B1000="Ongoing")*(D2:D1000="Whatev er")*(K2:K1000))
and this to count:
=SUMPRODUCT((B2:B1000="Ongoing")*(D2:D1000="Whatev er"))

You cannot use full columns in SUMPRODUCT and the ranges must be the same
size.

Hope this helps.
Andy.

"Tom Hewitt" wrote in message
...
Help!

I'm trying to get a formula that will work out the following, without
inserting any new columns on sheets I'm looking at.

I Need to Sum and count when

Column B = "Ongoing"
Column D = (This is variour different types of customer)

The column I need totalled is Column K.

It also needs counting so I can work out what the average order interval
is.

I can get it to work if I add another column which uses B&D, together.
But
Ideal I want a sep formula to work it out.

Any Ideas?

Sum/Count Multiple columns
 

Thanks for the feedback!

Andy.

"Tom Hewitt" wrote in message
...
Brilliant!!

Works fantastic. Didn't realise you couldnt use full columns in
sumproduct!

thanks for your help


"AndyB" wrote:

Hi

Try something like this to SUM:
=SUMPRODUCT((B2:B1000="Ongoing")*(D2:D1000="Whatev er")*(K2:K1000))
and this to count:
=SUMPRODUCT((B2:B1000="Ongoing")*(D2:D1000="Whatev er"))

You cannot use full columns in SUMPRODUCT and the ranges must be the same
size.

Hope this helps.
Andy.

"Tom Hewitt" wrote in message
...
Help!

I'm trying to get a formula that will work out the following, without
inserting any new columns on sheets I'm looking at.

I Need to Sum and count when

Column B = "Ongoing"
Column D = (This is variour different types of customer)

The column I need totalled is Column K.

It also needs counting so I can work out what the average order
interval
is.

I can get it to work if I add another column which uses B&D, together.
But
Ideal I want a sep formula to work it out.

Any Ideas?

Sum/Count Multiple columns
 

Andy,

While the formula works. For some reason it doesnt on all the tabs I'm
looking at. I have 12 tabs named Jan, Feb, Mar etc

The formulas work on Jan, Feb, Jun, Jul, Dec

But dont work on any of the other tabs.

Cheers

"AndyB" wrote:

Hi

Try something like this to SUM:
=SUMPRODUCT((B2:B1000="Ongoing")*(D2:D1000="Whatev er")*(K2:K1000))
and this to count:
=SUMPRODUCT((B2:B1000="Ongoing")*(D2:D1000="Whatev er"))

You cannot use full columns in SUMPRODUCT and the ranges must be the same
size.

Hope this helps.
Andy.

"Tom Hewitt" wrote in message
...
Help!

I'm trying to get a formula that will work out the following, without
inserting any new columns on sheets I'm looking at.

I Need to Sum and count when

Column B = "Ongoing"
Column D = (This is variour different types of customer)

The column I need totalled is Column K.

It also needs counting so I can work out what the average order interval
is.

I can get it to work if I add another column which uses B&D, together.
But
Ideal I want a sep formula to work it out.

Any Ideas?

Sum/Count Multiple columns
 

Hi Tom

What do you mean by 'dont work'? Do you get an error, a zero, or what?

Andy.

"Tom Hewitt" wrote in message
...
Andy,

While the formula works. For some reason it doesnt on all the tabs I'm
looking at. I have 12 tabs named Jan, Feb, Mar etc

The formulas work on Jan, Feb, Jun, Jul, Dec

But dont work on any of the other tabs.

Cheers

"AndyB" wrote:

Hi

Try something like this to SUM:
=SUMPRODUCT((B2:B1000="Ongoing")*(D2:D1000="Whatev er")*(K2:K1000))
and this to count:
=SUMPRODUCT((B2:B1000="Ongoing")*(D2:D1000="Whatev er"))

You cannot use full columns in SUMPRODUCT and the ranges must be the same
size.

Hope this helps.
Andy.

"Tom Hewitt" wrote in message
...
Help!

I'm trying to get a formula that will work out the following, without
inserting any new columns on sheets I'm looking at.

I Need to Sum and count when

Column B = "Ongoing"
Column D = (This is variour different types of customer)

The column I need totalled is Column K.

It also needs counting so I can work out what the average order
interval
is.

I can get it to work if I add another column which uses B&D, together.
But
Ideal I want a sep formula to work it out.

Any Ideas?

Sum/Count Multiple columns
 

Sorry,

I get a #DIV/0! error, but only on some of the months in one of the files.
So far as i can tell the tabs are all the same, nothing is different on them

"AndyB" wrote:

Hi Tom

What do you mean by 'dont work'? Do you get an error, a zero, or what?

Andy.

"Tom Hewitt" wrote in message
...
Andy,

While the formula works. For some reason it doesnt on all the tabs I'm
looking at. I have 12 tabs named Jan, Feb, Mar etc

The formulas work on Jan, Feb, Jun, Jul, Dec

But dont work on any of the other tabs.

Cheers

"AndyB" wrote:

Hi

Try something like this to SUM:
=SUMPRODUCT((B2:B1000="Ongoing")*(D2:D1000="Whatev er")*(K2:K1000))
and this to count:
=SUMPRODUCT((B2:B1000="Ongoing")*(D2:D1000="Whatev er"))

You cannot use full columns in SUMPRODUCT and the ranges must be the same
size.

Hope this helps.
Andy.

"Tom Hewitt" wrote in message
...
Help!

I'm trying to get a formula that will work out the following, without
inserting any new columns on sheets I'm looking at.

I Need to Sum and count when

Column B = "Ongoing"
Column D = (This is variour different types of customer)

The column I need totalled is Column K.

It also needs counting so I can work out what the average order
interval
is.

I can get it to work if I add another column which uses B&D, together.
But
Ideal I want a sep formula to work it out.

Any Ideas?