Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mutliple Function Query - probably using Countif
Hi,
I am trying to create a summary report detailing the number and value of new projects dependent upon whether they are live/closed and by the stage they are at or ended on. A sample of the data looks like: A B C 1 Status Stage Price 2 Closed Tender £890k 3 Active EOI £300k 4 Active BOT 5 Active BOT 6 Closed BOT £180k and the report table I am trying to create curerntly looks like: ACTIVE Stage No. Contract Value BOT 3 £180k EOI 1 £300k PQQ 0 £k Tender 1 £890k Interview 0 £k Awarded 0 £k Total: 5 £1,370k and a similiar table for closed projects. However, orginally I was not differentiating between active and closed so created a COUNTIF formula to answer how many projects at each stage e.g. three BOT's as shown above. I also used a SUMIF to provide the total value of contracts by stage type. Now I want to expand both of these so that they only return results for active cases - table 1 - and closed cases - table 2. I am guessing I need an IF or AND formula combined with the originals but need guidance how to write this please? I have tried using answers to other questions on hear but no joy - but am sure one of you more experienced people will be abel to quickyl point me int eh right direction. Thank you in advance for any assistance you can provide! Damo 8o) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mutliple Function Query - probably using Countif
=SUMPRODUCT(--(Tablesheet!$B$2:$B$5000 = A3),--(Tablesheet!$A$2:$A$5000=$B$1))
will give count of active BOTs. This presumes your first table is on a sheet named Tablesheet and is in cells A1 through C5000. Please retain the range. Do not change to B:B and A:A, as SUMPRODUCT is not a fan. This also assumes your results table is set up on a separat sheet with A3 as your first Stage name and B1 showing the actual word "Active". =SUMPRODUCT(--(Tablesheet!$B$2:$B$5000 = A2),--(Tablesheet!$A$2:$A$5000=$B$1),$C$2:$C$5000) same as above, thsi gives total contract value for active only. "Damo" wrote: Hi, I am trying to create a summary report detailing the number and value of new projects dependent upon whether they are live/closed and by the stage they are at or ended on. A sample of the data looks like: A B C 1 Status Stage Price 2 Closed Tender £890k 3 Active EOI £300k 4 Active BOT 5 Active BOT 6 Closed BOT £180k and the report table I am trying to create curerntly looks like: ACTIVE Stage No. Contract Value BOT 3 £180k EOI 1 £300k PQQ 0 £k Tender 1 £890k Interview 0 £k Awarded 0 £k Total: 5 £1,370k and a similiar table for closed projects. However, orginally I was not differentiating between active and closed so created a COUNTIF formula to answer how many projects at each stage e.g. three BOT's as shown above. I also used a SUMIF to provide the total value of contracts by stage type. Now I want to expand both of these so that they only return results for active cases - table 1 - and closed cases - table 2. I am guessing I need an IF or AND formula combined with the originals but need guidance how to write this please? I have tried using answers to other questions on hear but no joy - but am sure one of you more experienced people will be abel to quickyl point me int eh right direction. Thank you in advance for any assistance you can provide! Damo 8o) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mutliple Function Query - probably using Countif
Thank you Sean - great answer and really helped!
best wishes, Damo "Sean Timmons" wrote: =SUMPRODUCT(--(Tablesheet!$B$2:$B$5000 = A3),--(Tablesheet!$A$2:$A$5000=$B$1)) will give count of active BOTs. This presumes your first table is on a sheet named Tablesheet and is in cells A1 through C5000. Please retain the range. Do not change to B:B and A:A, as SUMPRODUCT is not a fan. This also assumes your results table is set up on a separat sheet with A3 as your first Stage name and B1 showing the actual word "Active". =SUMPRODUCT(--(Tablesheet!$B$2:$B$5000 = A2),--(Tablesheet!$A$2:$A$5000=$B$1),$C$2:$C$5000) same as above, thsi gives total contract value for active only. "Damo" wrote: Hi, I am trying to create a summary report detailing the number and value of new projects dependent upon whether they are live/closed and by the stage they are at or ended on. A sample of the data looks like: A B C 1 Status Stage Price 2 Closed Tender £890k 3 Active EOI £300k 4 Active BOT 5 Active BOT 6 Closed BOT £180k and the report table I am trying to create curerntly looks like: ACTIVE Stage No. Contract Value BOT 3 £180k EOI 1 £300k PQQ 0 £k Tender 1 £890k Interview 0 £k Awarded 0 £k Total: 5 £1,370k and a similiar table for closed projects. However, orginally I was not differentiating between active and closed so created a COUNTIF formula to answer how many projects at each stage e.g. three BOT's as shown above. I also used a SUMIF to provide the total value of contracts by stage type. Now I want to expand both of these so that they only return results for active cases - table 1 - and closed cases - table 2. I am guessing I need an IF or AND formula combined with the originals but need guidance how to write this please? I have tried using answers to other questions on hear but no joy - but am sure one of you more experienced people will be abel to quickyl point me int eh right direction. Thank you in advance for any assistance you can provide! Damo 8o) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF function query | Excel Worksheet Functions | |||
Mutliple Criteria Count function | Excel Worksheet Functions | |||
Countif Query | Excel Discussion (Misc queries) | |||
COUNTIF function query for multiple colums | Excel Worksheet Functions | |||
Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) |