SUMIF or COUNTIF???

johnb1979

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

johnb1979

New Member
Joined
Dec 9, 2019
Messages
37
Office Version
  1. 365
Platform
  1. Windows
OK, thank you. I'll try and work through it - I'm a bit of a novice as you can guess and am struggling with this one.

Thanks anyway
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,860
Office Version
  1. 365
Platform
  1. Windows
Have you tried what StuLux suggested?
 

johnb1979

New Member
Joined
Dec 9, 2019
Messages
37
Office Version
  1. 365
Platform
  1. Windows
I've tried it but I don't think I explained myself very well in the first instance so his suggestion may well work, but I've got to try and understand the formula first before I can apply it to my workbook.

I'm starting it again from scratch and will see if I can apply it then - as I say, my excel knowledge is fairly basic so maybe it's my form that needs redesigning.

Thanks anyway
 

johnb1979

New Member
Joined
Dec 9, 2019
Messages
37
Office Version
  1. 365
Platform
  1. Windows
Hi again,

Sorry to be a pain but I really need help with this. If you could help it'd be much appreciated.

I started my workbook again. I have a 'criteria' tab (please see the 1st image below), a '2020 Overview' tab (2nd image), followed by the month tabs on the 3rd image (I've only created 'Jan 2020' & 'Feb 2020' for now till I get it working).

Using the formula suggestion from StuLux, I've come up with the following formula but I don't know why it's returning an error. Just so you know, I created a named range for the tab names called 'Tab_Name' (only includes 'Jan 2020' & 'Feb 2020' from the list on the criteria tab for now).

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

If I can crack this then I'll have cracked the whole form.

Thanks for your help - if you need any further clarifications then please just let me know.

Image 1
1594738598025.png

Image 2
1594738638485.png

Image 3
1594738677750.png
 

Fluff

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

ADVERTISEMENT

The #Ref error indicates that one of the sheet names is wrong, or that you have blank cells in the named range.
 

johnb1979

New Member
Joined
Dec 9, 2019
Messages
37
Office Version
  1. 365
Platform
  1. Windows
You beauty!! That's got it working, thanks so much!!!

I've just got the rest of the form to complete now - wish me luck!
 

Fluff

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

ADVERTISEMENT

Glad we could help & thanks for the feedback.

However I would recommend limiting the ranges. Using whole column references in an array formulae is a bad idea.
 

johnb1979

New Member
Joined
Dec 9, 2019
Messages
37
Office Version
  1. 365
Platform
  1. Windows
OK, thank you. I don't think my knowledge will stretch as far as understanding why it's a bad idea but I'll keep an eye on the form in case it does anything funny.

Thanks again, this has been a great help!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,860
Office Version
  1. 365
Platform
  1. Windows
It will hog resources & can slow your workbook down.
 

johnb1979

New Member
Joined
Dec 9, 2019
Messages
37
Office Version
  1. 365
Platform
  1. Windows
Ah, OK thank you. I'll keep my eye out for that, then at least I'll understand the reason for it if it happens.

Thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,130,142
Messages
5,640,367
Members
417,139
Latest member
bdmprasenjit

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