Excel CountIf() or SumIf() with multi criteria 12

[jdegeorge]

Hi

I have a spreadsheet where I want to perform some conditional counting based on the values of 2 different columns.

For example, I have these columns:

[tt]Event Category Type
Dividends Technical Policy
Tax Technical Procedure
Tax New Policy
etc.

I performed CountIf() on CATEGORY and TYPE columns to get the number of times each value appeared (TECHNICAL 2, NEW 1, POLICY 2, PROCEDURE 1). That was the easy part.

What I need is to know the number of times a CATEGORY appears for each TYPE. I tried CountIf and SumIf but I don't know how to include multiple conditions.

CountIf(B1:B3 "Technical" & C1:C3 "Policy&quot or something like this.

Is it possible? There are way too many rows to do this manually! :-(


Jim DeGeorge

 

[xlbo]

hey Loomah - it's starting again ;-)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SkipVought]

Geoff,

I concur wholeheartedly!

I like to use named ranges. How sweet it is!

=SUMPRODUCT((LEFT(Category,4)="Tech")*(Type="Policy"))

I never realized I could do stuff like this!!!

Skip,

http://www.TheOfficeExperts.com

 

[jdegeorge]

Well, I did a quick filter on the columns and the dropdown box only showed "SERVICE" as an option. If "SERVICE " existed, wouldn't it have shown both in the dropdown?

Jim DeGeorge

 

[SkipVought]

Jim,

Not a good test!

Use Edit/Find and enter the string with a following space.

Skip,

http://www.TheOfficeExperts.com

 

[Loomah]

I think auto filter trims the text.
You could also incorp the trim function into your function

=SUMPRODUCT((TRIM(A1:A10)="SERVICE&quot*(B1:B10="Low&quot)
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[jdegeorge]

Skip

Not only did I find a few SERVICE with extra space, but there were a few other categories with extra spaces.

Must teach my users how to type!

Thanks, and enjoy the star!

Jim DeGeorge

 

[jdegeorge]

Loomah

Fixing the formula with TRIM is easier than removing the spaces, and easier than teaching others how to type!

Thanks, and enjoy the star.

Jim DeGeorge

 

[SkipVought]

Loomah,

A good solution for this problem, but I believe in data integrity, and I am sure you would agree.

Some of my time as an analyst is identifying bad data and recommending means to 1) correct the source of the problem and 2) correct the corrupt data (which in some cases requires costly manual intervention)

So identification and correction is important.



Skip,

http://www.TheOfficeExperts.com

 

[xlbo]

Skip - it is totally sweet
I got a "database" dynamically named TotDB and each column has its own dynamic range name

Then, I have a summary grid full of formulae that look like
=sumproduct((ROD=A1)*(AREA=B1)*(STATUS="ACQ&quot)

Just makes 'em soooo much easier to read and understand

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[KenWright]

I spent some time a while ago pulling various examples of SUMPRODUCT syntax from the newsgroups for both SUMPRODUCT and SUMIF, and given the comments in the thread re SUMPRODUCT, - Hopefully they will be useful (Text as an argument without quotes probably indicates a range name):-

SumProduct - Examples of Syntax

=SUMPRODUCT(($A$1:$A$100="L&quot*(CODE($B$1:$B$100)=66))
=SUMPRODUCT(($A$1:$A$100="L&quot*(CODE(UPPER($B$1:$B$100))=66))
=SUMPRODUCT(($A$1:$A$100="L&quot*(LEFT($B$1:$B$100,1)="b&quot)
=SUMPRODUCT(($B$6:$B$14=E6)*($C$6:$C$14=F6)*($D$6:$D$14))
=SUMPRODUCT((A1:A100="apples&quot*(C1:C100="Spain&quot*(D1100))
=SUMPRODUCT((A1:A100>=$E$1)*(A1:A100<=$E$2)*(B1:B100=$E$3)*(C1:C100=$E$4)*D1
100)
=SUMPRODUCT((A1:A100>=1)*(A1:A100<=100)*A1:A100)/SUMPRODUCT((A1:A100>=1)*(A1
:A100<=100))
=SUMPRODUCT((A1:A20<0)*(B1:B20))/SUMPRODUCT((A1:A20<0)*1)
=SUMPRODUCT((A1:A20=13)*(B1:B20>6)*C1:C20)
=SUMPRODUCT((A1:A20=2003)*(B1:B20=1)*C1:C20)
=SUMPRODUCT((A1:A365>=DATEVALUE(&quot;10/01/2002&quot) *
(A1:A365<DATEVALUE(&quot;11/01/2002&quot) * (C1:C365))
=SUMPRODUCT((A1:A4=&quot;X&quot*(B1:B4=&quot;N&quot*C1:C4)
=SUMPRODUCT((A2:A100=&quot;red&quot+(A2:A100=&quot;blue&quot+(A2:A100=&quot;yellow&quot,B2:B100)
=SUMPRODUCT((A2:A100={&quot;red&quot;,&quot;blue&quot;,&quot;yellow&quot;})*B2:B100)
=SUMPRODUCT((A2:A105=D1)+(A2:A105=E1)+(A2:A105=F1),B2:B105)
=SUMPRODUCT((A2:A120={&quot;red&quot;,&quot;blue&quot;,&quot;yellow&quot;})*(B2:B120))
=SUMPRODUCT((A2:A120=D1:F1)*(B2:B120))
=SUMPRODUCT((A2:A120=D1:F1)*(B2:B150))
=SUMPRODUCT((A2:B100=&quot;YourClass&quot*(B2:B100=&quot;YourSize&quot*C2:C100)
=SUMPRODUCT((A8:A200=&quot;Jan&quot*(B2:B10=&quot;Week 1&quot*H8:H200)
=SUMPRODUCT((A9:A25=&quot;blue&quot*ABS(C9:C25))
=SUMPRODUCT((B40:AE40=TRUE)*(B41:AE41=&quot;V&quot)
=SUMPRODUCT((dts>st)*(dts<=nd)*(B4:M4))
=SUMPRODUCT((EXACT(LEFT(data,1),&quot;s&quot+0))
=SUMPRODUCT((LEFT(INDIRECT($B23&&quot;!H2:H4500&quot,4)=C$3)*(INDIRECT($B23&&quot;!F2:F45
00&quot=C$2),INDIRECT($B23&&quot;!G2:G4500&quot)
=SUMPRODUCT((MOD(ROW(A1:A97),7)=0)*A1:A97)
=SUMPRODUCT((MOD(ROW(A1:A97),7)=1)*A1:A97)
=SUMPRODUCT((MOD(ROW(Range)-CELL(&quot;Row&quot;,Range)+H1,H2)=0)*(Range))
=SUMPRODUCT((MONTH(A1:A100)=6)*B1:B100)
=SUMPRODUCT((MONTH(A1:A300)=2)*(ISNUMBER(A1:A300)))
=SUMPRODUCT((MONTH(A1:A500)=1)*(B1:B500))
=SUMPRODUCT((MONTH(A8:A21)=MONTH(G3))*(B8:B21))
=SUMPRODUCT((MONTH(B2:B9)=11)*(A2:A9=&quot;Pending&quot)
=SUMPRODUCT((range1=&quot;L&quot*(LEFT(range2)=&quot;B&quot)
=SUMPRODUCT((Range1=A1)*(Range2=B1)*(Range3=C1))
=SUMPRODUCT((RNGA=&quot;A&quot*(RNGB=1)*RNGC)
=SUMPRODUCT((Sheet2!A1:A100>=Sheet1!A1)*(Sheet2!A1:A100<=DATE(YEAR(Sheet1!A1
),MONTH(Sheet1!A1)+Sheet1!A2-1,DAY(Sheet1!A1)))*(Sheet2!B1:B100))
=SUMPRODUCT((TEXT(B1:B5,&quot;mmm&quot=&quot;Oct&quot*A1:A5)
=SUMPRODUCT((YEAR(B2:B9)=2002)*(MONTH(B2:B9)=11)*(A2:A9=&quot;Pending&quot)
=SUMPRODUCT(A2:A5;B2:B5)
=SUMPRODUCT(A2:A56,B2:B56)+SUMPRODUCT(A58:A62,B58:B62)+SUMPRODUCT(A64:A75,B6
4:B75)
=SUMPRODUCT(ABS(A1:A10)*1)
=SUMPRODUCT(COUNTIF(INDIRECT(&quot;Week&quot;
&(ROW(INDIRECT(&quot;1:11&quot))&&quot;!D419&quot,&quot;CORP&quot)
=SUMPRODUCT(MOD(COLUMN(I8:IS8),2),I8:IS8)
=SUMPRODUCT(MONTH(B1:B5=9)*A1:A5)
=SUMPRODUCT(N(C2:C765={&quot;B&quot;,&quot;TB&quot;}))
=SUMPRODUCT(N(EXACT(C2:C765,{&quot;B&quot;,&quot;TB&quot;})))
=SUMPRODUCT(SUMIF(INDIRECT(&quot;'Respondent
NO&quot;&ROW(INDIRECT(&quot;1:50&quot)&&quot;'!C1&quot,1,INDIRECT(&quot;'Respondent
NO&quot;&ROW(INDIRECT(&quot;1:50&quot)&&quot;'!A1&quot))
=SUMPRODUCT(SUMIF(INDIRECT(ROW(1:35)&&quot;!B1&quot,TRUE,INDIRECT(ROW(1:35)&&quot;!A5&quot))
=SUMPRODUCT(x^(ROW(INDIRECT(&quot;1:&quot;&CEILING(T,1)))*{1,-1}+T*{0,1}))
=SUMPRODUCT((A1:A6>C1)*B1:B6)

=SUMPRODUCT((MOD(COLUMN(J3:IV3)-3,7)=0)*(J3:IV3))
Every 7th column

=SUMPRODUCT((MOD(ROW(A1:A10),2)=0)*(A1:A10))
To find every 2nd row, starting with row 2 (will add rows 2, 4, 6, 8, and
10):

=SUMPRODUCT((MOD(ROW(A1:A10),2)=1)*(A1:A10))
To find every 2nd row, starting with row 1 (will add rows 1, 3, 5, 7, and
9):

=SUMPRODUCT((MOD(ROW(A1:A10),3)=0)*(A1:A10))
To find every 3rd row, starting with row 3 (will add rows 3, 6, and 9): To
find every 4th, 5th, 6th, etc..., starting with row 4, 5, 6, etc..., just
change the number 3 in the above formula to 4, 5, 6, etc.

=SUMPRODUCT((MOD(ROW(A1:A10),3)=1)*(A1:A10))
To find every 3rd row, starting with row 1 (will add rows 1, 4, 7, and 10):
To find every 4th, 5th, 6th, etc..., starting with row 1, just change the
number 3 in the above formula to 4, 5, 6, etc.



SUMIF - Examples of Syntax

=SUMIF($A$2:$A$6,&quot;>06/01/02&quot;,$B$2:$B$6)
=SUMIF($A$2:$A$6,$C2&$D2,$B$2:$B$6)
=SUMIF($A1:$A6,FALSE,C1:C6)
=SUMIF($A1:$A6,TRUE,C1:C6)
=SUMIF($B$2:$M$2,&quot;<=&quot;&mth,$B4:$M4)
=SUMIF($P$5:$P$4630,&quot;81*LC&quot;,DB$5B$4630)-SUMIF($P$5:$P$4630,&quot;81*LLC&quot;,DB$5
B$4630) WILDCARDS *,?
=SUMIF(---,&quot;>&quot;&A75,---)
=SUMIF(A1:A10,1)
=SUMIF(A1:A100,&quot;>&quot;&MAX(A1:A100)-7,B1:B100)
=SUMIF(A1:A20,&quot;<0&quot;,B1:B20)/COUNTIF(A1:A20,&quot;<0&quot
=SUMIF(A1:A4,&quot;<>#N/A&quot/MAX(1,COUNT(A1:A4))
=SUMIF(A1:A6,&quot;>&quot;&C1,B1:B6)
=SUMIF(A1:A6,&quot;>&quot;&DATEVALUE(&quot;6/30/01&quot,B1:B6)
=SUMIF(A1:C11,&quot;>&quot;&E2)
=SUMIF(A1:C11,&quot;>&quot;&sheet2!E2)
=SUMIF(A2:A25,&quot;>=&quot;&E2,B2:B25)-SUMIF(A2:A25,&quot;>&quot;&F2,B2:B25)
=SUMIF(A2:A5,&quot;>&quot;&D1,B2:B5)-SUMIF(A2:A5,&quot;>&quot;&E1,B2:B5)
=SUMIF(A3:A13,&quot;>0&quot+SUMIF(A3:A13,&quot;<0&quot
=SUMIF(A7:A48.&quot;T&quot;.E7:E48)
=SUMIF(B2:B13,&quot;>&quot;&A75)
=SUMIF(C1:C4,&quot;LC&quot;,B1:B4)
=SUMIF(D5:F11,&quot;>70&quot;,D5:F11)
=SUMIF(Details!$A$1:$A$999,$A2,Details!B$2:B$999)
=SUMIF(E2:E8;&quot;>&quot;&TODAY();F2:F8)
=SUMIF(MKTCAP,&quot;>=5000000000&quot - SUMIF(MKTCAP,&quot;>20000000000&quot
=SUMIF(P25,&quot;<>*LLC&quot;,DB2B5) WILDCARDS *,?
=SUMIF(range,&quot;>=&quot; & &quot;2/1/2001&quot;,sum_range) - SUMIF(range,&quot;>&quot;
&&quot;2/28/2001&quot;,sum_range)
=SUMIF(Range,>1 billion,Range) - SUMIF(Range,>5 Billion,Range)
=SUMIF(TheRange,&quot;<0)
=SUMIF(therange,&quot;>0&quot

Regards
Ken..................


----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[jdegeorge]

Ken

Let me be the first of many to give you a star for this list!

Jim DeGeorge

 

[KenWright]

I used to crib from it all the time, but every time I look through it, I still end up saying to myself &quot;I forgot you could do that!!&quot;

Some of them shouldn't be taken literally, but hopefully these are obvious, eg:-

=SUMIF(Range,>1 billion,Range) - SUMIF(Range,>5 Billion,Range)

Range in this case is simply a descriptor for a real range or for a range name, and the 1 Billion bit was probably the poster not knowing how many 0s to put in

Regards
Ken......................



----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[vaneagle]

A Star from me as well! Most helpful!

 

[vaneagle]

Actually I have another sumif to add which I think is quite useful.

=SUMIF(A31:A37,&quot;>-1e9&quot

Basically this will sum the selected range ignoring text, #DIV/0!, & #REF! within the selected range.

eg

1
5
#DIV/0!
6
text
#REF!

Answer = 12 using the formula =SUMIF(A1:A6,&quot;>-1e9&quot

 

[jdegeorge]

Vaneagle

I added it to the list from Ken. Nice one! Enjoy the star.

Jim DeGeorge

 

[Loomah]

Skip
Re data integrety I do agree wholeheartedly!
Cases like this I've used (when I had a job) a little snippet of code to do the tidying for me.

For info, for the thread

for each c in range(&quot;YourRange&quot
c = trim(c)
next

or something like that!

Ken
Great list. Something I should've done long ago but I'm simply too lazy!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[rdodge]

Just so you all know, you can use up to 7 levels of nested functions regardless if the formula involves arrays or not. I have used them quite a bit until I ran into some additional problems to where I needed more levels than 7 levels of nested functions, so I ended up turning to VBA to resolve my issues. Normally, one does not run into this issue until they start making their data rather dynamic to take into account the various things that needs to be taken into account, some of which not only deals with how data is entered such as Jim's situation here, but also to help keep from having error messages as the result of the formulae.

Just as a side note, Jim, your name made me think of JD Edwards with the &quot;jde&quot; part of your handle on here.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[jdegeorge]

Ronald

JD Edwards. I wish! I'd be rich after the PeopleSoft acquisition!

As for needing more than 7 nests, if I ever had a spreadsheet that was that complicated I shoot mysefl, but convert it to Access first (my true love!).

Jim DeGeorge

 

[rdodge]

Believe it or not, I am currently working on developing a manufacturing DB program which uses Access, but would eventually like to see it converted over to some other more suited DB program.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[hammers1]

I have 2 columns column A when a ticket was opened and column b, when a ticket was closed.

what I would like to get is a total of tickets the were open between two date ranges for this example 01/02/2005 to 28/02/2005
They would need to have been opened before 28/01/2005 and not closed between the date range ( as I need to see any tickets that are opened during that time but not closed)
From the example below it would pick up 2 tickets

open Closed
01/01/2005 01/01/2005
01/02/2005 01/02/2005
01/03/2005 01/03/2005
01/04/2005 01/04/2005
01/05/2005 01/05/2005
01/06/2005 01/06/2005
01/07/2005 01/07/2005
01/01/2005
01/02/2005
01/03/2005
01/04/2005
01/05/2005
01/06/2005
01/07/2005