Sumproduct based on two conditions of 1 one column

**keis386** · 07-30-2012, 10:47 AM

H

What I am trying count is:

How many dates have not been received at 16 July (cell J2)?

- For this we have to look at all trades smaller or same as 16 July so <=J2 (in column H)
- Of these cells only the cells which have an value "yes" in column G
- How many of these cells have a date above 16 July or an empty date in column F
This last part is the only part I do not know how to solve it.

See attached Excel File

I want to do it with sumproduct and not with countifs , due to other restrictions

I have tries option like:

SUMPRODUCT((H2:H75<=J2)*(G2:G75="yes")*(F2:FH275>J2&" "))
SUMPRODUCT((H2:H75<=J2)*(G2:G75="yes")*(F2:F75<J2&F2:F75=" "))

Hope someone can help me

**Pete_UK** · 07-30-2012, 11:05 AM

I think this is what you need:

=SUMPRODUCT((H2:H75<=J2)*(G2:G75="yes")*(F2:F75>J2)*(F2:F75<>""))

Hope this helps.

Pete

---------- Post added at 04:05 PM ---------- Previous post was at 04:04 PM ----------

I think this is what you need:

=SUMPRODUCT((H2:H75<=J2)*(G2:G75="yes")*(F2:F75>J2)*(F2:F75<>""))

Hope this helps.

Pete

**Fotis1991** · 07-30-2012, 11:05 AM

Hi

Did you try?

=SUMPRODUCT((H2:H75<=J2)*(G2:G75="yes")*(F2:F75>J2)*(F2:F75<>""))

Edit! Pete:This time, you were faster.

**keis386** · 07-30-2012, 11:15 AM

Works excellent thanks, I thought I tried that option already!

**Pete_UK** · 07-30-2012, 11:23 AM

Originally Posted by Fotis1991

Edit! Pete:This time, you were faster.

Yes, but I got the "you cannot post within 30 seconds - try again in 15 seconds" message, so I ended up double-posting !!

Pete

**Teethless mama** · 07-30-2012, 11:56 AM

Originally Posted by Pete_UK

I think this is what you need:

=SUMPRODUCT((H2:H75<=J2)*(G2:G75="yes")*(F2:F75>J2)*(F2:F75<>""))

Hope this helps.

Pete

---------- Post added at 04:05 PM ---------- Previous post was at 04:04 PM ----------

I think this is what you need:

=SUMPRODUCT((H2:H75<=J2)*(G2:G75="yes")*(F2:F75>J2)*(F2:F75<>""))

Hope this helps.

Pete

you no need to include the last piece (F2:F75<>"") , when you evaluate (F2:F75>J2)
So the formula can be shorten

=SUMPRODUCT((H2:H75<=J2)*(G2:G75="yes")*(F2:F75>J2))

**Pete_UK** · 07-30-2012, 12:19 PM

Hi Mama,

not seen you posting for ages - I remember you from the News Group days. Hope you are well.

Pete

**keis386** · 07-31-2012, 07:34 AM

Hi,

If I change the date in cell J2 to 21 July, it should give an number, but everything after 20 July turns out to 0.
How can I change that. There are still empty cells in column F, so the formula should count these (only the empty cells that also satisfy the other conditions).

**Fotis1991** · 07-31-2012, 07:48 AM

Hi

One reason, is

=SUMPRODUCT((H2:H75<=J2)*(G2:G75="yes")*(F2:F75>J2)*(F2:F75<>""))

In column F, biggest date is 21/07.....

**keis386** · 07-31-2012, 07:54 AM

Yes I get this, but the whole problem was that column F has two conditions: is bigger than J2 or cell is empty.

=SUMPRODUCT((H2:H75<=J2)*(G2:G75="yes")*(F2:F75>J2)*(F2:F75<>""))

**Pete_UK** · 07-31-2012, 08:03 AM

If you want OR, then you can do it this way:

=SUMPRODUCT((H2:H75<=J2)*(G2:G75="yes")*((F2:F75>J2)+(F2:F75<>"")))

Hope this helps.

Pete

**Fotis1991** · 07-31-2012, 08:07 AM

Originally Posted by keis386

Yes I get this, but the whole problem was that column F has two conditions: is bigger than J2 or cell is empty.

=SUMPRODUCT((H2:H75<=J2)*(G2:G75="yes")*(F2:F75>J2)*(F2:F75<>""))

This is AND. Not OR.

Try

=SUMPRODUCT((H2:H75<=J2)*(G2:G75="yes")*(F2:F75>J2))+SUMPRODUCT((H2:H75<=J2)*(G2:G75="yes")*(F2:F75=""))

**keis386** · 07-31-2012, 09:59 AM

This is really excellent, why did I not think about this myself!

I am sorry but I have still one minor question:
If I add an value in column H and G the formula automatic adds a cell, because it is a table.
But for column F the newest cell is mostly empty how can I make sure that the formula also adds one cell here? Like:

=SUMPRODUCT((H2:H75<=J2)*(G2:G75="yes")*(F2:F75>J2))+SUMPRODUCT((H2:H75<=J2)*(G2:G75="yes")*(F2:F75= ""))
So if I add a cell new formula will add a cell --> Should be like:
=SUMPRODUCT((H2:H76<=J2)*(G2:G76="yes")*(F2:F76>J2))+SUMPRODUCT((H2:H76<=J2)*(G2:G76="yes")*(F2:F76= ""))

**Fotis1991** · 07-31-2012, 10:10 AM

One way could be this.

=SUMPRODUCT((H2:H1175<=J2)*(G2:G1175="yes")*(F2:F1175>J2)*(G2:G1175<>""))+SUMPRODUCT((H2:H1175<=J2)*(G2:G1175="yes")*(F2:F1175= "")*(G2:G1175<>""))

Replace the 1175 row, with any row number you want..

**keis386** · 07-31-2012, 10:23 AM

Thanks I appriciate this a lot!!

**Fotis1991** · 07-31-2012, 10:27 AM

You are welcome!

I am happy that my suggestions work for you!

Sumproduct based on two conditions of 1 one column

LinkBack

Thread Tools

Rate This Thread

Display

Sumproduct based on two conditions of 1 one column

Re: Sumproduct based on two conditions of 1 one column

Re: Sumproduct based on two conditions of 1 one column

Re: Sumproduct based on two conditions of 1 one column

Re: Sumproduct based on two conditions of 1 one column

Re: Sumproduct based on two conditions of 1 one column

Re: Sumproduct based on two conditions of 1 one column

Re: Sumproduct based on two conditions of 1 one column

Re: Sumproduct based on two conditions of 1 one column

Re: Sumproduct based on two conditions of 1 one column

Re: Sumproduct based on two conditions of 1 one column

Re: Sumproduct based on two conditions of 1 one column

Re: Sumproduct based on two conditions of 1 one column

Re: Sumproduct based on two conditions of 1 one column

Re: Sumproduct based on two conditions of 1 one column

Re: Sumproduct based on two conditions of 1 one column

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions