# SUMIF or COUNTIF???

#### johnb1979

##### New Member
Hello all,

I'm hoping this is a simple one for one of you Excel wizards!!

I would like to add the value of a cell in a given column when 2 different criteria are met in adjacent cells in adjacent columns.

For example, if a cell in column A reads a £ value, and a cell in column B reads 'won', and a cell in column C reads 'JANUARY', I'd like D1 to show the total £'s of all cells in column A that meet this criteria.

I need to use the same formula for each month of the year.

Hope this is clear and an easy one for you to answer.

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### StuLux

##### Well-known Member
I'm assuming that what you are saying is that in Column D you want a total of amounts in Column A if 1) The month in Column C is JANUARY 2) The status in Column b is "Won", if so then this formula will give you that:
=SUMIFS(A:A,C:C,"JANUARY",B:B,"Won")
Instead of hard-coding the month as "JANUARY" you could create 12 cells with the month names in them and then point the formula to each of these cells to give totals for each month.

#### CA Vijay Gupta

##### New Member
Hello John,

Sumifs will work here..

 Value Position Month Total Criteria 1 Won £76,522.00​ Won January £76,522.00​ Criteria 2 January £58,349.00​ Loss February £89,996.00​ Won March £21,346.00​ Won April £72,799.00​ Won May £74,734.00​ Loss June £24,672.00​ Loss July £5,571.00​ Loss August £64,414.00​ Loss September £604.00​ Won October £56,496.00​ Loss November £32,060.00​ Loss December £44,685.00​ Loss January £72,689.00​ Loss February £20,090.00​ Loss March £44,393.00​ Loss April

#### CA Vijay Gupta

##### New Member
 Value Position Month Total Formula Criteria 1 Won £41,554.00​ Won January £41,554.00​ =SUMIFS(\$A\$2:\$A\$17,\$B\$2:\$B\$17,\$H\$1,\$C\$2:\$C\$17,\$H\$2) Criteria 2 January £8,803.00​ Loss February £52,644.00​ Won March £81,571.00​ Won April £69,952.00​ Won May £87,672.00​ Loss June £95,986.00​ Loss July £70,390.00​ Loss August £26,235.00​ Loss September £71,501.00​ Won October £51,188.00​ Loss November £57,732.00​ Loss December £78,153.00​ Loss January £28,507.00​ Loss February £53,940.00​ Loss March £96,023.00​ Loss April

#### johnb1979

##### New Member
Thank you both so much for your help.

I've used the first example that was provided to see how that goes. The form is a little more complex than the initial example I gave. I've used the structure of your example and come up with the following formula;

=SUMIFS('Jan 2020:Dec 2020'!D:D,'Jan 2020:Dec 2020'!I:I,"JANUARY",'Jan 2020:Dec 2020'!H:H,"WON")

......but the cell comes back with '#VALUE!'.

Essentially, the sheet has 12 tabs (one for each month), and another tab that collates and displays the data. It's a sales tracker basically - a month could be quoted in January but won in February. The salesperson will go back to where they quoted the job, select 'won' and then the month it was won. I'd then like this to display in main sheet in the relevant 'won' month where the full info in collated.

I'm sure it's nearly there, I just wish I knew excel as well as you good guys!

Any advice will be greatly appreciated.

Thanks again,

#### CA Vijay Gupta

##### New Member
Can you please share Base data

#### johnb1979

##### New Member
Thank you - Is this OK for you to use?

#### StuLux

##### Well-known Member
A possible solution:
Create a named range containing the names of your 12 tabs - in my workings I have called this named range "Months"
The following formula can then be used:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Months&"'!D:D"),INDIRECT("'"&Months&"'!I:I"),"JANUARY",INDIRECT("'"&Months&"'!H:H"),"Won"))

Link below for an explanation of how this works
How to Use the SUMIF Function Across Multiple Sheets | Excelchat

#### johnb1979

##### New Member
Thank you. That doesn't seem to work they way I want it to unfortunately.

This is where I'm at with it at the moment.

This formula works great for just the 'Jan 2020' tab;

=SUMIFS('Jan 2020'!D:D,'Jan 2020'!I:I,"JANUARY",'Jan 2020'!H:H,"won")

...but I would like it to also include jobs from the other month tabs that have the month 'January' selected in column 'I' when the job is 'won' in column 'H' on the respective tab. I thought the following would work but it just returns an #VALUE! error;

=SUMIFS('Jan 2020:Dec 2020'!D:D,'Jan 2020:Dec 2020'!I:I,"JANUARY",'Jan 2020:Dec 2020'!H:H,"won")

I hope this is all making sense and I really do appreciate your input on this...I'm sure it only needs a tweak and it'll work fine.

Thanks again

#### Fluff

##### MrExcel MVP, Moderator
You cannot use sumifs across sheets like that, you need to do it like StuLux has suggested.

Replies
12
Views
301
Replies
0
Views
51
Replies
0
Views
37
Replies
2
Views
135
Replies
21
Views
359

1,181,598
Messages
5,930,800
Members
436,761
Latest member
mintwaxed

### 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