Countifs or Sumifs

[RalstonT14612]

Hello,

I am a novice with excel and I am trying to get a formula to work with array and criteria.

The formula I am using is:
=SUM(COUNTIFS('Kevin Accounts'!$B$2:$B$560,">="&'Formula Data'!A2,'Kevin Accounts'!$B$2:$B$560,"<="&'Formula Data'!A6,'Kevin Accounts'!$C$2:$C$560,{"Live with Support","100% complete},'Kevin Accounts'!$I$2:$I$560,,{"HT RED","HT Halifax","Legacy Diversity","HT AURORA 1 OR 2","DATA ENTRY ROCH","HT Roch","WELLS/BANK","NYS","STORE KIT","GOVERNMENT/FED"},'Kevin Accounts'!$J$2:$J$560,{"Implementation","Restructure"))

This is a long formula and I am not sure of any other way to do it. I will also need a second formula to count a specific dollar amount that falls with in this criteria as well.

Any help would be greatly appreciated. Thank you.

[daffodil11]

I'd recommend liberal use of the Name Manager under the Formulas tab to shorten your formulas considerably.

For example, you can set the word "Accounts" to represent: ={"HT RED","HT Halifax","Legacy Diversity","HT AURORA 1 OR 2","DATA ENTRY ROCH","HT Roch","WELLS/BANK","NYS","STORE KIT","GOVERNMENT/FED"}

Also, use Alt+Enter to organize your formulas. It will make them easy to troubleshoot and edit.

Cleaned up with linebreaks and using just three defined names you could end up with:

=SUM(COUNTIFS('Kevin Accounts'!$B$2:$B$560,">="&'Formula Data'!A2,
	      'Kevin Accounts'!$B$2:$B$560,"<="&'Formula Data'!A6,
	      'Kevin Accounts'!$C$2:$C$560,Support,
	      'Kevin Accounts'!$I$2:$I$560,Accounts,
	      'Kevin Accounts'!$J$2:$J$560,Implementation))

Which looks fine.

[daffodil11]

However, if you're beginning to branch out into arrays I'd highly recommend you use SUMPRODUCT.

It doesn't require the use of CSE braces unless you nest a few certain expressions within it, and it does everything COUNTIF, SUMIF and more.

Count of cells where the criteria are met:

=SUMPRODUCT(('Kevin Accounts'!$B$2:$B$560>='Formula Data'!A2)*
	    ('Kevin Accounts'!$B$2:$B$560<='Formula Data'!A6)*
	    ('Kevin Accounts'!$C$2:$C$560=Support)*
	    ('Kevin Accounts'!$I$2:$I$560=Accounts)*
	    ('Kevin Accounts'!$J$2:$J$560=Implementation))

Now let's pretend you have your dollars out in Column K and you want to sum that where criteria are met:

=SUMPRODUCT(('Kevin Accounts'!$B$2:$B$560>='Formula Data'!A2)*
	    ('Kevin Accounts'!$B$2:$B$560<='Formula Data'!A6)*
	    ('Kevin Accounts'!$C$2:$C$560=Support)*
	    ('Kevin Accounts'!$I$2:$I$560=Accounts)*
	    ('Kevin Accounts'!$J$2:$J$560=Implementation)*
	    ('Kevin Accounts'!$K$2:$K$560))

[RalstonT14612]

Hello,

Thanks for your quick response.

Using this suggestion it returned 0 as the result.

=SUM(COUNTIFS('Kevin Accounts'!$B$2:$B$560,">="&'Formula Data'!A2,
'Kevin Accounts'!$B$2:$B$560,"<="&'Formula Data'!A6,
'Kevin Accounts'!$C$2:$C$560,Support,
'Kevin Accounts'!$I$2:$I$560,Accounts,
'Kevin Accounts'!$J$2:$J$560,Implementation))

Using this suggestion I received #N/A as the result

=SUMPRODUCT(('Kevin Accounts'!$B$2:$B$560>='Formula Data'!A2)*
('Kevin Accounts'!$B$2:$B$560<='Formula Data'!A6)*
('Kevin Accounts'!$C$2:$C$560=Support)*
('Kevin Accounts'!$I$2:$I$560=Accounts)*
('Kevin Accounts'!$J$2:$J$560=Implementation))

Thanks!

[daffodil11]

Did you define the ranges with those names?

[RalstonT14612]

I apologize I am not sure what you mean about define? I am still very new to this formula function. I want it to look at all information with this list (Support, Accounts, Implementation).

[daffodil11]

Click the Formulas Tab.

Click Name Manager.

Here you can make a word equal to a range or a set of variables


For example, you can set PrettyPinkUnicorns to equal =A1:A50
And then whenever you want to refer to A1:A50, you could just type PrettyPinkUnicorns.

Let's take your first multicriteria: {"Live with Support","100% complete}

You can create a defined name in the Name Manager called SadPanda, set equal to ={"Live with Support","100% complete}

Now instead of having to type out {"Live with Support","100% complete} every time, you can just put SadPanda.
As an added bonus, if that criteria changes, you just update the defined name and it changes for every formula

[RalstonT14612]

I did sent a range for each list.

example:
Support is Live with Support and 100% complete

Accounts is: HT RED, HT Halifax, Legacy Diversity,HT AURORA 1 OR 2,DATA ENTRY ROCH,HT Roch,WELLS/BANK,NYS,STORE KIT,GOVERNMENT/FED

I have attached my spread sheet. I added the list to the formula tab.

[daffodil11]

Can you attach a small workbook sample so I can see what's wrong?

[RalstonT14612]

I attached the workbook call Kevin- Tracker 2015

[daffodil11]

I apologize, but after a few hours of research I am unable to find any way in which a named range of more than one cell can be applied as a criteria.

I will see if I can trouble one of the old masters for some advice.


Barring a more elegant solution, I'd run with the SUMPRODUCT version of your original.

COUNT

=SUMPRODUCT(('Kevin Accounts'!$B$2:$B$560>='Formula Data'!A2)*
('Kevin Accounts'!$B$2:$B$560<='Formula Data'!A6)*
('Kevin Accounts'!$C$2:$C$560={"Live with Support","100% complete"})*
('Kevin Accounts'!$I$2:$I$560=
	{"HT RED","HT Halifax","Legacy Diversity","HT AURORA 1 OR 2","DATA ENTRY ROCH","HT Roch","WELLS/BANK","NYS","STORE KIT","GOVERNMENT/FED"})*
('Kevin Accounts'!$J$2:$J$560={"Implementation","Restructure"))

SUM OF H

=SUMPRODUCT(('Kevin Accounts'!$B$2:$B$560>='Formula Data'!A2)*
('Kevin Accounts'!$B$2:$B$560<='Formula Data'!A6)*
('Kevin Accounts'!$C$2:$C$560={"Live with Support","100% complete"})*
('Kevin Accounts'!$I$2:$I$560=
	{"HT RED","HT Halifax","Legacy Diversity","HT AURORA 1 OR 2","DATA ENTRY ROCH","HT Roch","WELLS/BANK","NYS","STORE KIT","GOVERNMENT/FED"})*
('Kevin Accounts'!$J$2:$J$560={"Implementation","Restructure")*
('Kevin Accounts'!$H$2:$H$560))

[XOR LX]

Hi.

I won't unfortunately have time to look into the specifics of your question right now, though perhaps you could read this in the meantime:

http://excelxor.com/2014/09/28/count...iteria_ranges/

It appears that you are under the impression that you can enter as many criteria - where those criteria are intended as "OR" criteria - for as many different ranges as you wish using COUNTIFS, which is not necessarily so.

If someone hasn't resolved your query by tomorrow then I'll take another look.

Regards

[Jonmo1]

It appears that you are under the impression that you can enter as many criteria - where those criteria are intended as "OR" criteria - for as many different ranges as you wish using COUNTIFS, which is not necessarily so.

+1
I'm only aware of being able to do 2 sets of {or} criteria in a sum/countIFs formula.
AND, those 2 criteria arrays need to be transposed of each other, 1 a column, the other a row..

=SUM(COUNTIFS(range1,{a,b,c,d},range2,{1;2;3}))
Notice one {array} is using comma seperators, the other is using semicolons.

You can't do 3.

I'd suggest
=SUM(COUNTIFS(range1,{a,b,c}))+SUM(COUNTIFS(range2,{1,2,3}))+SUM(COUNTIFS(range3,{8,9,10}))

[RalstonT14612]

Hi Jonmo1,

The first solution is working for me with 2 as you stated. I tried to do the 3 alternate and it started doubling and tripling the count for some reason. When I needed to do more than three I just kept the third criteria to a single text and then copied the formula for the second selection. If that makes sense.

[Tony Valko]

Here's another one...

Put all the criteria in cells...

Data Range

	A	B	C
1
2	Live with Support	HT RED	Implementation
3	100% complete	HT Halifax	Restructure
4		Legacy Diversity
5		HT AURORA 1 OR 2
6		DATA ENTRY ROCH
7		HT Roch
8		WELLS/BANK
9		NYS
10		STORE KIT
11		GOVERNMENT/FED
12

Then the formula becomes:

=SUMPRODUCT(--('Kevin Accounts'!$B$2:$B$560>='Formula Data'!A2),--('Kevin Accounts'!$B$2:$B$560<='Formula Data'!A6),--ISNUMBER(MATCH('Kevin Accounts'!$C$2:$C$560,A2:A3,0)),--ISNUMBER(MATCH('Kevin Accounts'!$I$2:$I$560,B2:B11,0)),--ISNUMBER(MATCH('Kevin Accounts'!$J$2:$J$560,C2:C3,0)))

[daffodil11]

This is what I spent hours trying to do. This is awesome.

Then the formula becomes:

=SUMPRODUCT(--('Kevin Accounts'!$B$2:$B$560>='Formula Data'!A2),--('Kevin Accounts'!$B$2:$B$560<='Formula Data'!A6),--ISNUMBER(MATCH('Kevin Accounts'!$C$2:$C$560,A2:A3,0)),--ISNUMBER(MATCH('Kevin Accounts'!$I$2:$I$560,B2:B11,0)),--ISNUMBER(MATCH('Kevin Accounts'!$J$2:$J$560,C2:C3,0)))

[Tony Valko]

This is what I spent hours trying to do. This is awesome.

Put an example in your stash.

[RalstonT14612]

How would I make this work if I needed to use the criteria and counting $ amounts?

[daffodil11]

This returns a count of 2 for B13:

=SUMPRODUCT(('Kevin Accounts'!$B$2:$B$560>='Formula Data'!A2)*
('Kevin Accounts'!$B$2:$B$560<='Formula Data'!A6)*
(ISNUMBER(MATCH('Kevin Accounts'!$C$2:$C$560,Status,0)))*
(ISNUMBER(MATCH('Kevin Accounts'!$I$2:$I$560,Accounts,0)))*
(ISNUMBER(MATCH('Kevin Accounts'!$J$2:$J$560,Implementation,0))))

And this SUMS for H:

=SUMPRODUCT(('Kevin Accounts'!$B$2:$B$560>='Formula Data'!A2)*
('Kevin Accounts'!$B$2:$B$560<='Formula Data'!A6)*
(ISNUMBER(MATCH('Kevin Accounts'!$C$2:$C$560,Status,0)))*
(ISNUMBER(MATCH('Kevin Accounts'!$I$2:$I$560,Accounts,0)))*
(ISNUMBER(MATCH('Kevin Accounts'!$J$2:$J$560,Implementation,0)))*
('Kevin Accounts'!$H$2:$H$560))

[RalstonT14612]

I think you solved my problem! Thank you everyone, I was going insane!

[Jonmo1]

Yep that makes sense..

Basically sumif(1storcriteria)+sumif(2ndorcriteria)

[karedog]

Hi,

If you want to avoid helper cells, then this is the array formula (so need to ended with Ctrl-Shift-Enter) :
(I put this array formula on Formula Data!L8 at attached file)

=SUMPRODUCT( ('Kevin Accounts'!$B$2:$B$560 >= 'Formula Data'!A2) *
             ('Kevin Accounts'!$B$2:$B$560 <= 'Formula Data'!A6) *
             IFERROR(FIND("|"&'Kevin Accounts'!$C$2:$C$560&"|", "|Live with Support|100% complete|")^0,FALSE) *
             IFERROR(FIND("|"&'Kevin Accounts'!$I$2:$I$560&"|", "|HT RED|HT Halifax|Legacy Diversity|HT AURORA 1 OR 2|DATA ENTRY ROCH|HT Roch|WELLS/BANK|NYS|STORE KIT|GOVERNMENT/FED|")^0,FALSE) *
             IFERROR(FIND("|"&'Kevin Accounts'!$J$2:$J$560&"|", "|Implementation|Restructure|")^0,FALSE)
           )

Regards

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.