Sum/Count Multiple columns

T

Tom Hewitt

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?
 
G

Guest

Hi

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

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

Hope this helps.
Andy.
 
T

Tom Hewitt

Brilliant!!

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

thanks for your help
 
G

Guest

Thanks for the feedback!

Andy.

Tom Hewitt said:
Brilliant!!

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

thanks for your help
Click to expand...
 
T

Tom Hewitt

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
 
T

Tom Hewitt

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
 
Top