SUMIF or COUNTIF???

johnb1979

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

Thank you in advance!!!!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

StuLux

Well-known Member
Joined
Sep 14, 2005
Messages
645
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 22, 2020
Messages
14
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
Hello John,

Sumifs will work here..


ValuePositionMonthTotalCriteria 1Won
£76,522.00​
WonJanuary
£76,522.00​
Criteria 2January
£58,349.00​
LossFebruary
£89,996.00​
WonMarch
£21,346.00​
WonApril
£72,799.00​
WonMay
£74,734.00​
LossJune
£24,672.00​
LossJuly
£5,571.00​
LossAugust
£64,414.00​
LossSeptember
£604.00​
WonOctober
£56,496.00​
LossNovember
£32,060.00​
LossDecember
£44,685.00​
LossJanuary
£72,689.00​
LossFebruary
£20,090.00​
LossMarch
£44,393.00​
LossApril
 

CA Vijay Gupta

New Member
Joined
Jun 22, 2020
Messages
14
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
ValuePositionMonthTotalFormulaCriteria 1Won
£41,554.00​
WonJanuary
£41,554.00​
=SUMIFS($A$2:$A$17,$B$2:$B$17,$H$1,$C$2:$C$17,$H$2)Criteria 2January
£8,803.00​
LossFebruary
£52,644.00​
WonMarch
£81,571.00​
WonApril
£69,952.00​
WonMay
£87,672.00​
LossJune
£95,986.00​
LossJuly
£70,390.00​
LossAugust
£26,235.00​
LossSeptember
£71,501.00​
WonOctober
£51,188.00​
LossNovember
£57,732.00​
LossDecember
£78,153.00​
LossJanuary
£28,507.00​
LossFebruary
£53,940.00​
LossMarch
£96,023.00​
LossApril
 

johnb1979

New Member
Joined
Dec 9, 2019
Messages
39
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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,

1594653375740.png
 

johnb1979

New Member
Joined
Dec 9, 2019
Messages
39
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thank you - Is this OK for you to use?

1594654545176.png
 

StuLux

Well-known Member
Joined
Sep 14, 2005
Messages
645
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 9, 2019
Messages
39
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
58,139
Office Version
  1. 365
Platform
  1. Windows
You cannot use sumifs across sheets like that, you need to do it like StuLux has suggested.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,646
Messages
5,654,567
Members
418,142
Latest member
peterappiahkubi

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