Sumif Formula or Sumproduct Formula? Three Criteria.

1. Sumif Formula or Sumproduct Formula? Three Criteria.

I'll try my best to keep this very simply and not over complicate.

I have a column containing dates, format dd/mm/yyyy. This is column B. Range B2:B1000.
I have a column containing data limited to only "NEW", "EXISTING" OR "LAPSED (+12 MONTHS)". This is column E. Range E2:E1000.
I have a column containing data limited to only "WON", "OPEN", "LOST" OR "DEAD". This is column J. Range J2:J1000.
I have a column containing monetary values. This is column H. Range H2:H1000.

Question: The return answer needs to the sum total of monetary value, identified as "LAPSED" AND "WON" AND within a specific month.

I need various other outcomes, such as "NEW" AND "WON" AND within a specific month but I'm sure I can figure those variables out on my own once I have a starting place from the above.

Many Thanks

2. Re: Sumif Formula or Sumproduct Formula? Three Criteria.

Without looking at your attached file, your description does not include a column that can contain "WON", so where does that criterion come from?

Pete

3. Re: Sumif Formula or Sumproduct Formula? Three Criteria.

Originally Posted by Pete_UK
Without looking at your attached file, your description does not include a column that can contain "WON", so where does that criterion come from?

Pete
Hi Pete, sorry I wasn't clear. Column J has the WON etc entries. Original post revised

4. Re: Sumif Formula or Sumproduct Formula? Three Criteria.

This should answer your question. Let me know if you have any other questions.

oHUTCHYo.xlsx

5. Re: Sumif Formula or Sumproduct Formula? Three Criteria.

hi oHUTCHYo, try:
=SUMPRODUCT((TEXT('Q1'!\$B2:\$B1000,"MMYYYY")=TEXT(B1,"MMYYYY"))*('Q1'!\$E\$2:\$E\$1000="LAPSED (+12 MONTHS)")*('Q1'!\$J\$2:\$J\$1000="Won")*('Q1'!\$H\$2:\$H\$1000))

for every condition you have, simply add in *(range=criteria)

ps: there is also no need to convert the whole columns as a Table. the purpose of doing a Table is to make data dynamic. your range is supposed to change when you add data to the Table

6. Re: Sumif Formula or Sumproduct Formula? Three Criteria.

Big thanks to MitchC and benishiryo

Solved!

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1