sumif and countif?

[jaydywan]

A B
EG 2006
EG 2007
EH 2007

I have the following example. I want to count EG if B is 2007. This
example would yield 1. I utilized =if(B:B=2007, countif(A:A, "EG)).
This formula yields 2. I basically need a formula that will look at
both criterion. Thanks in advance.

 

[Guest]

Try this

=sumproduct(--(A:A="EG"),--(B:B=2007))

 

[Dewayne.Bridges]

A B
EG 2006
EG 2007
EH 2007

I have the following example. I want to count EG if B is 2007. This
example would yield 1. I utilized =if(B:B=2007, countif(A:A, "EG)).
This formula yields 2. I basically need a formula that will look at
both criterion. Thanks in advance.

This should do it:

Use DCountA

Your data sheet will need column headers.

In a seperate sheet (I create a sheet called "REFERENCE" and hide it)
put your criteria:

A B
1 TYPE DATE
2 EG 2007


Dim ws As Worksheet
Set ws = "yourdatasheet name here"

Dim refSheetRangeString As String
refSheetRangeString = "A1:B2"


Dim count As Variant
Dim countFilteredRows As Long
count = Empty
count = Application.DCountA(ws.UsedRange, countColumnHeader,
referenceSheet.Range(refSheetRangeString))
If (Not IsError(count)) Then
countFilteredRows = count
Else
countFilteredRows = 0
End If

I can send you a general purpose function that can do this on up to 5
columns if you want...

 

[Bob Phillips]

You cannot use complete columns in SUMPRODUCT, the ranges have to be
defined.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)