Sumproduct assistance - need multiple ANDs and ORs

H

Heidi

We are trying to count the number of rows where:
(1) the date is one of two dates (date1 OR date2) AND
(2) a column is one of two values (value1 OR value2)

I am familiar with using sumproduct to do either AND (*) or OR (+), but am
not sure how to approach mixing the two. Does anyone have any suggestions?

Thanks,

Heidi
 
B

Bob Phillips

=SUMPRODUCT(--((C1:C20=date1)+(C1:C20=date2)),--((D1:D20="value1")+(D1:D20="value2")))

--
---
HTH

Bob

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

RagDyeR

Try this:

=SUMPRODUCT(((A1:A30=C1)+(A1:A30=C2))*((B1:B30=D1)+(B1:B30=D2)))

Just group the arguments correctly with parens.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

We are trying to count the number of rows where:
(1) the date is one of two dates (date1 OR date2) AND
(2) a column is one of two values (value1 OR value2)

I am familiar with using sumproduct to do either AND (*) or OR (+), but am
not sure how to approach mixing the two. Does anyone have any suggestions?

Thanks,

Heidi
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sumproduct Help! 3
Sumproduct Problem 1
Nesting SUBTOTAL with SUMPRODUCT? 1
Finding MIN value in a column between Two Dates in different columns 3
Question about using MIN/MAX, INDEX with Date range 3
Query the same table three times 0
Count Blank Cells with Conditions 3
Possible Sumproduct with Or Function 1

Top