Add 1 if two criteria are met across multiple tabs

johnb1979

Board Regular
Joined
Dec 9, 2019
Messages
50
Office Version
  1. 365
Platform
  1. Windows
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'.

1595862563043.png


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.

1595862788466.png



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!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
so use the countifs formula in Summary Sheet.

But you have 3 Row Items "OV" "AMR" and "Line". Is this any kind ceriteria???
 
Upvote 0
Thanks both!

I'm currently using the following formula to total up the 'OV' £ on the summary page and wondered if this could be tweaked for my requirements now?

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Tab_Name&"'!E:E"),INDIRECT("'"&Tab_Name&"'!I:I"),"Won",INDIRECT("'"&Tab_Name&"'!J:J"),"January"))

Please note I didn't come up with this formula - it was another excel wizard that gave me that! :)

The OV, AMR & Line columns won't really affect the formula requirements on this one - I just want to total the number of jobs won in any given month, the OV, AMR & Line will become irrelevant in this scenario.

Thank you both for coming back so quickly. I look forward to your replys.
 
Upvote 0
=COUNTIFS(statuscolumn,"Won",monthswoncolumn,celltoleft)
 
Upvote 0
=SUMPRODUCT(SUMIFS(INDIRECT("'"&Tab_Name&"'!E:E"),INDIRECT("'"&Tab_Name&"'!I:I"),"Won",INDIRECT("'"&Tab_Name&"'!J:J"),"January"))

Why do you need Indirect function and other stuff. I Believe it is simple countif stuff
 
Upvote 0
How about
=SUMPRODUCT(COUNTIFS(INDIRECT("'"&Tab_Name&"'!I:I"),"Won",INDIRECT("'"&Tab_Name&"'!J:J"),"January"))
 
Upvote 0
Why do you need Indirect function and other stuff. I Believe it is simple countif stuff
The Indirect is because it's looking at multiple different sheets
 
Upvote 0
But then as per OP the data is available in only one sheet (Sales Active Form).
 
Upvote 0
Have a look at the thread title. ;)
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top