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

#### johnb1979

##### New Member
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
Have you tried what StuLux suggested?

#### johnb1979

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

Image 2

Image 3

#### Fluff

##### MrExcel MVP, Moderator

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

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
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
It will hog resources & can slow your workbook down.

#### johnb1979

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

Replies
16
Views
187
Replies
5
Views
345
Replies
1
Views
152
Replies
7
Views
76
Replies
2
Views
112

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.

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