Mutliple Function Query - probably using Countif

[Damo]

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)

 

[Sean Timmons]

=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]

Thank you Sean - great answer and really helped!

best wishes,
Damo