Add 1 if two criteria are met across multiple tabs

johnb1979

New Member
Joined
Dec 9, 2019
Messages
37
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
558
Office Version
  1. 365
Platform
  1. Windows
Countifs statement should handle that.
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
900
Office Version
  1. 365
Platform
  1. Windows
so use the countifs formula in Summary Sheet.

But you have 3 Row Items "OV" "AMR" and "Line". Is this any kind ceriteria???
 

johnb1979

New Member
Joined
Dec 9, 2019
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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.
 

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
558
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

=COUNTIFS(statuscolumn,"Won",monthswoncolumn,celltoleft)
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
900
Office Version
  1. 365
Platform
  1. Windows
=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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,861
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,861
Office Version
  1. 365
Platform
  1. Windows
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
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
900
Office Version
  1. 365
Platform
  1. Windows
But then as per OP the data is available in only one sheet (Sales Active Form).
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,861
Office Version
  1. 365
Platform
  1. Windows
Have a look at the thread title. ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,143
Messages
5,640,384
Members
417,140
Latest member
whiteprose

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
Top