SUMIF or COUNTIF???

johnb1979

Board Regular
Joined
Dec 9, 2019
Messages
50
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You cannot use sumifs across sheets like that, you need to do it like StuLux has suggested.
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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