SUMIF or COUNTIF???

[johnb1979]

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]

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

 

[Fluff]

Have you tried what StuLux suggested?

 

[johnb1979]

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]

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]

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

 

[johnb1979]

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]

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]

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]

It will hog resources & can slow your workbook down.

 

[johnb1979]

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