COUNTIF and SUMPRODUCT (I think..)

B

BY70

New Member
Joined
Aug 12, 2011
Messages
5
Hello (clever) people (hopefully flattery will work??)

Very frustrated over what I think should be an easy fix.

I have columns of data, the first shows a "tiering" and the second a series of dates.

I need to be able to count the number of instances of each tier in each Month / Year.

I have been using

=SUMPRODUCT(--(TEXT(h_LTSB!$E:$E,"mmm")="Feb"),--(TEXT(h_LTSB!$E:$E,"yy")="11")) to determine the number of instances of the Month / Year, yet am failing dismally when trying to consider the number of instances of each Tier.

The results of the following extract should be:

Feb 2011 - Tier 2 = 0
Feb 2011 - Tier 3 = 1
Mar 2011 - Tier 2 = 1
Mar 2011 - Tier 3 = 2
Apr 2011 - Tier 2 = 2
Apr 2011 - Tier 3 = 3
Apr 2012 - Tier 2 = 1

<TABLE style="WIDTH: 104pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=139 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #a5a5a5" width=64 height=20>Tier</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5" width=75>Date</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>3</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>26/02/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>3</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>12/03/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>2</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>12/03/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>3</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>19/03/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>3</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>04/04/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>3</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>09/04/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>3</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>14/04/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>2</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>23/04/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>2</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>23/04/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>2</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>23/04/2012</TD></TR></TBODY></TABLE>

Have gone passed swearing at laptop and am close to throwing it away and buying a pen and paper!

Any help would be gratefully received!

Thanks

Bruce
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Welcome to the board...

You can combine the 2 text functions (1 for month, 1 for year) into 1 text function
=SUMPRODUCT(--(TEXT(h_LTSB!$E:$E,"mmmyy")="Feb11"))

Then, assuming the tier is in column D...Try this for Feb11 Tier 2

=SUMPRODUCT(--(TEXT(h_LTSB!$E:$E,"mmmyy")="Feb11"),--(h_LTSB!$D:$D=2))

Hope that helps...


Note, I know you're allowed to use entire column refs like E:E in 2007 and above, but It's still not recommended...
Best to use finite ranges like E1:E1000
 
Upvote 0
jonmo1 said:
Then, assuming the tier is in column D...Try this for Feb11 Tier 2

=SUMPRODUCT(--(TEXT(h_LTSB!$E:$E,"mmmyy")="Feb11"),--(h_LTSB!$D:$D=2))
Click to expand...


Hi,
In this example, I modified the formula as I need to specify more than 1 Tier. For example, I want to specify only Tier 2,3 & 7.
I tried this but Excels keeps telling me it runs out of resources & cant calculate.

=SUMPRODUCT((TEXT(h_LTSB!$E:$E,"mmmyy")="Feb11")*(h_LTSB!$D:$D={2,3,7}))

Previously I used Countifs instead of Sumproduct (Countifs Tier2 + Countifs Tier3 + Countifs Tier3). But formula is getting too long. Any suggestions?
 
Upvote 0
Considering the message..
I'd suggest NOT using Entire column references.

Instead of D:D, use D1:D1000 - or whatever the applicable range is
 
Upvote 0
jaysan3 said:
Hi,
In this example, I modified the formula as I need to specify more than 1 Tier. For example, I want to specify only Tier 2,3 & 7.
I tried this but Excels keeps telling me it runs out of resources & cant calculate.

=SUMPRODUCT((TEXT(h_LTSB!$E:$E,"mmmyy")="Feb11")*(h_LTSB!$D:$D={2,3,7}))

Previously I used Countifs instead of Sumproduct (Countifs Tier2 + Countifs Tier3 + Countifs Tier3). But formula is getting too long. Any suggestions?
Click to expand...

Try...
Rich (BB code): 
=SUMPRODUCT(
   --(TEXT(h_LTSB!$E:$E,"mmmyy")="Feb11"),
   --ISNUMBER(MATCH(h_LTSB!$D:$D,{2,3,7},0)))

Note. It would be better to have definite ranges instead of whole columns for better performance.
 
Upvote 0
Great. It works much faster. Thanks a lot. Actually I was using defined names, but the selection was the whole sheet. Reduced it to fit my selection & it is much faster. Thanks again.
 
Upvote 0
Hi, I am trying to modify the formula to include wildcard and am stuck with it. How do I use wildcard for multiple criterias?

=SUMPRODUCT((TEXT(ENTRYDATE,"mmmyy")=$B6)*(DEPT=$A6)*(RATYPE={1,"FA-W","INST*","LOAN*"}))

or

=SUMPRODUCT(--(TEXT(ENTRYDATE,"mmmyy")=$B6),--(DEPT=$A6), --ISNUMBER(MATCH(RATYPE,{1,"FA-W","INST*","LOAN*"},0)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps

Forum statistics

Threads
1,224,604
Messages
6,179,857
Members
452,948
Latest member
UsmanAli786

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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