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]

2 Options

1st and easiest is to put a pivot table over the dat
2nd is to use SUMPRODUCT

=SUMPRODUCT((Range1=var1)*(Range2=Var2))
where ranges1 & 2 are your ranges with the criteria.

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

 

[bluedragon2]

=SUMPRODUCT(($B$1:$B$4="Technical&quot*($C$1:$C$4="Policy&quot)



Blue

If I wasn't Blue, I would just be a Dragon...

 

[jdegeorge]

Blue and xlbo

SUMPRODUCT seems to be what I need, but it's not working with the wildcard. For example, sometimes the category isn't standard. It could be "SERVICE" or "SERVICE/PRODUCT" so I want to look for "SER*".

I keep getting 0 as the value. Can this be done?

In the meantime, enjoy the stars!

Jim DeGeorge

 

[bluedragon2]

Expand your SUMPRODUCT to include both:

SUMPRODUCT((range="Technical&quot*(Range="Service&quot*(Range="Service/Product&quot)



Blue

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

opps, wrote that wrong:

=SUMPRODUCT((range="Technical&quot*(Range="Service&quot)+SUMPRODUCT((range="Technical&quot*(Range="Service/Product&quot
)

Blue

If I wasn't Blue, I would just be a Dragon...

 

[xlbo]

=SUMPRODUCT((Left($B$1:$B$4,3)="SER&quot*($C$1:$C$4="Policy&quot)


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

 

[bluedragon2]

Good one Skip, didn't know you could add that into the sumproduct formula like that. SFU

I see that they combined the forums again. Glad to see that...



Blue

If I wasn't Blue, I would just be a Dragon...

 

[xlbo]

Skip ???


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

 

[bluedragon2]

opp, I meant good one Geoff... :~/

Blue

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

I felt that one! But NOTING happened in my STAR COUNT!

Actually, I wanted to bestow START on the BOTH of you, Goeff and Blue. I keep learning!


Skip,

http://www.TheOfficeExperts.com

 

[jdegeorge]

Gotta love the LEFT function! That worked great. Wish I could give you another star, but I see that Blue did it for me. Thanks!

Jim DeGeorge

 

[jdegeorge]

Sorry Geoff. Skip was helping me on another post and I got confused... :-(

Jim DeGeorge

 

[jdegeorge]

Wait a minute... I must REALLY be confused! It was BLUE who wrote SKIP, not me! But, you've all been a great help these past 2 weeks so I'll gladly take the fall for that one!

Jim DeGeorge

 

[jdegeorge]

Now, back to my problem...

I tried both of these formulas and the result should be the same, but they're different:

=SUMPRODUCT((LEFT($I$3:$I$300,3)="SER&quot*($E$3:$E$300="Low&quot)
=SUMPRODUCT(($I$3:$I$300="SERVICE&quot*($E$3:$E$300="Low&quot)

The first returns 13 and the second returns 12. The value for the column happens to be SERVICE for all of them. Hmmmm?

Jim DeGeorge

 

[SkipVought]

Blue? Geoff? Jim? Skip?

What's going on here?

I am too confused to rant and rave!



Skip,

http://www.TheOfficeExperts.com

 

[jdegeorge]

How'd I get a star? I was the one with the problem!

Jim DeGeorge

 

[xlbo]

does one of your entries happen to have a trailing space ??

On another note - I'm starting to luuuuurve sumproduct. I too thought you could only use nested functions in array formulae but forgot one day, lobbed it in a sumproduct formula and it worked - makes it a massively powerful function

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

 

[Loomah]

To whoever!
There may be a trailing space after one ofthe SERVICEs
ie "SERVICE " insted of "SERVICE"

just a thought
;-)

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

 

[bluedragon2]

That is one of the drawbacks of running functions against text. Exact matches with whitspace can be a pain in the neck.



Blue

If I wasn't Blue, I would just be a Dragon...