| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| using functions to compare multiple columns for mismatch of cells | Excel Worksheet Functions | |||
| making multiple columns when printing long datasheet | Excel Discussion (Misc queries) | |||
| Justify text across multiple columns | Excel Discussion (Misc queries) | |||
| Pivot Table combining multiple columns | Excel Discussion (Misc queries) | |||
| HOW TO MATCH MULTIPLE COLUMNS WITH OR WITHOUT GAPS IN eXCEL ? | Excel Worksheet Functions | |||
Hybrid Mode
