# Add 1 if two criteria are met across multiple tabs

#### johnb1979

##### New Member
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!

### 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
Countifs statement should handle that.

#### CA_Punit

##### Well-known Member
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
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

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

#### CA_Punit

##### Well-known Member
=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

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

#### Fluff

##### MrExcel MVP, Moderator
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
But then as per OP the data is available in only one sheet (Sales Active Form).

#### Fluff

##### MrExcel MVP, Moderator
Have a look at the thread title.

Replies
2
Views
85
Replies
1
Views
261
Replies
1
Views
152
Replies
23
Views
361
Replies
4
Views
718

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.

### Which adblocker are you using?

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

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