 # Count With Sumproduct And 3 Criteria Range

• Hi all

I am trying to count some data I stripped from SAP General ledger
I need to count a record if
Unit
Within range of GL Codes
Within date range

Here is the sumproduct formula I came up with
=SUMPRODUCT(--(Data!\$A\$2:\$A\$209=\$A11),--(AND(Data!\$C\$2:\$C\$209>=\$B\$1,Data!\$C\$2:\$C\$209<=\$B\$8)),--(AND(Data!\$J\$2:\$J\$209>=\$B\$10,Data!\$J\$2:\$J\$209<=\$C\$10)))

Where
Data! Contains the data to be analyzed
\$A is the unit number(s)
\$C is the GL Codes
\$B1 is the lowest GL code to be incl
\$B8 is the highest GL code to be incl
\$J is a range of dates
\$B10 is lowest date to be incl
\$C10 is the highest date to be incl

Currently the formula returns a #VALUE! error
Where did I go wrong?

Thanks
-marc

• Re: Count With Sumproduct And 3 Criteria Range

Howdy. I think you don't need the AND, maybe something like this?

=SUMPRODUCT(--(Data!\$A\$2:\$A\$209=\$A11)*--(Data!\$C\$2:\$C\$209>=\$B\$1)*--(Data!\$C\$2:\$C\$209<=\$B\$8)*--(Data!\$J\$2:\$J\$209>=\$B\$10),Data!\$J\$2:\$J\$209<=\$C\$10)))

Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
Humanware: Older than dirt

Old, slow, and confused - but at least I'm inconsistent!

Rich
(retired Excel 2003 user, 3.28.2008)

• Re: Count With Sumproduct And 3 Criteria Range

check this file i think can help

## Files

.
:chase:
Why not learn from the mistakes of others?--you can't make them all yourself!

• Re: Count With Sumproduct And 3 Criteria Range

Thanks guys -

Shades the formula did the job

Thanks
-marc

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!