Add 1 if two criteria are met across multiple tabs

[johnb1979]

Hi,

Thanks for your help on this one.

I'd like a formula that will add '1' when two criteria are met. Please see below example;

This is a sales tracking form. There are 3 jobs below that were quoted (and therefore 'active') in January and the column next to it, the month in which the quote was 'won'.

What I would like to happen is that in the 'summary' tab I have, if a job is ever marked as 'won', the total number of jobs appears in the relevant cell adjacent the relevant month (please see below). In this example, there should be a 1 in January and a 2 in February.

I hope this all makes sense and I appreciate any support you might be able to offer.

I look forward to hearing from you soon!

 

[johnb1979]

How about
=SUMPRODUCT(COUNTIFS(INDIRECT("'"&Tab_Name&"'!I:I"),"Won",INDIRECT("'"&Tab_Name&"'!J:J"),"January"))

This worked perfectly! Thanks @Fluff


Thank you all for your support on this one - really is appreciated! One day I'll hope to be half as good as you excel wizards!!

 

[johnb1979]

I've just realised something and it's a massive school boy error and my apologies to @CA_Punit as you highlighted this earlier. I'm sure it'll be just a tweak to @Fluff 's formula but I need the 'unit' (or add 1) to appear in the relevant box if the any of the 'won' values are greater than £0. In the example below, The units won table should display 1 in the OV cell for January, and 2 in the AMR cell for February.

Again, my apologies for my error - it appears you understand my form better than I do!!

 

[Fluff]

How about, for OV
=SUMPRODUCT(COUNTIFS(INDIRECT("'"&Tab_Name&"'!I:I"),"Won",INDIRECT("'"&Tab_Name&"'!J:J"),"January"),INDIRECT("'"&Tab_Name&"'!E:E"),">0")
Just change the E:E to F:F etc for the other groups

 

[johnb1979]

Thanks @Fluff

I've just tried it and it returned a #VALUE! error. Would you know what I might be doing wrong please?

Thanks again

 

[Fluff]

Without anything to test on, not sure.
Although it may be that you are using whole columns & so it's picking up text.

 

[johnb1979]

OK, thank you. I'll keep messing with it.

Thanks for all your help - it's been great!

 

[Fluff]

You're welcome.