Is this formula flawed

Alex O

Active Member
Joined
Mar 16, 2009
Messages
343
Office Version
  1. 365
Platform
  1. Windows
I'm using the formula below, which seems to work in some instances but not others!!!! Can someone take a look and tell if the formula is logical or not.

Thanks

=IF(Q4="",SUMIF($AC$4:$AC$37448,I4,$AE$4:$AE$37448),SUMPRODUCT(($AC$4:$AC$37448=I4)*($AB$4:$AB$37448>=Q4)*($AE$4:$AE37448)))
AAABACADAEAFAGAHAI
43855462/28/2011102056CHK2002620054146
53872123/14/2011102056CHK2002620054146
63939664/25/2011102056CHK2002620054146
74036086/27/2011102056CHK2002620054146
84091507/29/2011102056CHK2002620054146
94132508/29/2011102056CHK2002620054146
1042687711/28/2011102056CHK10081002773
1143132012/27/2011102056CHK100131002773
124094838/2/2011102535CHK174017430144
133840172/23/2011103875CHK2004020054146

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 51px;"><col style="width: 71px;"><col style="width: 58px;"><col style="width: 75px;"><col style="width: 50px;"><col style="width: 60px;"><col style="width: 50px;"><col style="width: 60px;"><col style="width: 82px;"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,143
Office Version
  1. 2007
Platform
  1. Windows
Formula seems logical.
Do you have error or unexpected (reversed) result?

If you have a zero instead of no value in Q4 then Q4="" will be FALSE. Maybe that is the cause.

If so, use OR():
IF(OR(Q4="",Q4=0),....
 

Alex O

Active Member
Joined
Mar 16, 2009
Messages
343
Office Version
  1. 365
Platform
  1. Windows
I tried your suggestion, but no luck. I did notice a few errors in the construction of the formula, but the corrections had no impact. The problem is that when there is a date in Q:Q it returns 0.
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
just copied your data.
Can you give example when the formula does not work?
You are mentioned Q:Q are you dragging this formula down?
Excel Workbook
EFGHIQAAABACADAE
410205629/11/201114/03/2011102056200
53425/04/201110205626
627/06/201110205626
729/07/201110205626
829/08/201110205626
929/11/201110205626
1027/12/20111020568
1108/02/201110253513
1223/02/20111038750
Sheet3
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

I tried your suggestion, but no luck. I did notice a few errors in the construction of the formula, but the corrections had no impact. The problem is that when there is a date in Q:Q it returns 0.
Your formula is syntactically correct. Whether it's flawed depends on what you're tying to do.

You say it retunrs 0 when there's a date entered in column Q.

...($AB$4:$AB$37448>=Q4)...

So, that would mean the above expression returns an entire array of FALSE which leads me to think that Q4 might not contain a true Excel date. If Q4 contains a true Excel date then:

=ISNUMBER(Q4)

Will return TRUE.

If the entries in AB4:AB37448 are all true Excel dates and Q4 is a TEXT string that looks like a date then:

...($AB$4:$AB$37448>=Q4)...

Will return an entire array of FALSE leading to a formula result of 0.
 

Alex O

Active Member
Joined
Mar 16, 2009
Messages
343
Office Version
  1. 365
Platform
  1. Windows
Here's a broader snapshot of my data. T. Valko, I tested your theory reg. the dates and all are true dates. The formula is being drug down to approx. Q120, not the entire column as I may have suggested in my earlier post.
The problem itself doesn't seem that complicated (although there are more variables that I like dealing with in R!) But with respect to the problem at hand, I simply need to look at Q and if there's no date envoke the first Sumif. If there is a date, I need to find the match value in I4 to AC and get the sum of only those payments that were received after the date in Q.

HIJKLMNOPQR
424679314582xxxxx5200314582-1 88,7630 11,539
524679240626xxxxx5050240626-1 #N/A3,2267/2/2010#N/A
624679187432xxxxx5050187432-1,187445-1,210758-1,210893-1,210983-1 #N/A2,6221/0/1900#N/A

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 54px;"><col style="width: 60px;"><col style="width: 291px;"><col style="width: 59px;"><col style="width: 70px;"><col style="width: 355px;"><col style="width: 24px;"><col style="width: 70px;"><col style="width: 82px;"><col style="width: 71px;"><col style="width: 96px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
O4=IF(Q4="",SUMIF($AC$4:$AC$37448,I4,$AE$4:$AE$37448),SUMPRODUCT(($AC$4:$AC$37448=I4)*($AB$4:$AB$37448>=Q4)*($AE$4:$AE37448)))
P4=SUMIF($B$4:$B$6813,I4,$E$4:$E$6813)
Q4=IF(ISNA(VLOOKUP($I4,$T$4:$Y$65536,5,FALSE)),"",VLOOKUP($I4,$T$4:$Y$65536,5,FALSE))
R4=IF(O4=0,"",IF(AND(O4>0,P4=0),O4*$Q$1,IF(AND(O4>0,P4>0,Q4<=$S$1),(O4+P4)*$Q$1+$T$2,IF(AND(O4>0,P4>0,Q4=""),(O4+P4)*$Q$1+$T$1,(IF(AND(O4>0,P4>0,Q4>$S$1),(O4+P4)*$Q$1+$T$1))))))+S4
O5=IF(Q5="",SUMIF($AC$4:$AC$37448,I5,$AE$4:$AE$37448),SUMPRODUCT(($AC$4:$AC$37448=I5)*($AB$4:$AB$37448>=Q5)*($AE$4:$AE37449)))
P5=SUMIF($B$4:$B$6813,I5,$E$4:$E$6813)
Q5=IF(ISNA(VLOOKUP($I5,$T$4:$Y$65536,5,FALSE)),"",VLOOKUP($I5,$T$4:$Y$65536,5,FALSE))
R5=IF(O5=0,"",IF(AND(O5>0,P5=0),O5*$Q$1,IF(AND(O5>0,P5>0,Q5<=$S$1),(O5+P5)*$Q$1+$T$2,IF(AND(O5>0,P5>0,Q5=""),(O5+P5)*$Q$1+$T$1,(IF(AND(O5>0,P5>0,Q5>$S$1),(O5+P5)*$Q$1+$T$1))))))+S5
O6=IF(Q6="",SUMIF($AC$4:$AC$37448,I6,$AE$4:$AE$37448),SUMPRODUCT(($AC$4:$AC$37448=I6)*($AB$4:$AB$37448>=Q6)*($AE$4:$AE37450)))
P6=SUMIF($B$4:$B$6813,I6,$E$4:$E$6813)
Q6=IF(ISNA(VLOOKUP($I6,$T$4:$Y$65536,5,FALSE)),"",VLOOKUP($I6,$T$4:$Y$65536,5,FALSE))
R6=IF(O6=0,"",IF(AND(O6>0,P6=0),O6*$Q$1,IF(AND(O6>0,P6>0,Q6<=$S$1),(O6+P6)*$Q$1+$T$2,IF(AND(O6>0,P6>0,Q6=""),(O6+P6)*$Q$1+$T$1,(IF(AND(O6>0,P6>0,Q6>$S$1),(O6+P6)*$Q$1+$T$1))))))+S6

<tbody>
</tbody>

<tbody>
</tbody>
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

Here's a broader snapshot of my data. T. Valko, I tested your theory reg. the dates and all are true dates. The formula is being drug down to approx. Q120, not the entire column as I may have suggested in my earlier post.
The problem itself doesn't seem that complicated (although there are more variables that I like dealing with in R!) But with respect to the problem at hand, I simply need to look at Q and if there's no date envoke the first Sumif. If there is a date, I need to find the match value in I4 to AC and get the sum of only those payments that were received after the date in Q.
Your formula is pretty straightforward.

When Q4 contains a date and the formula returns 0 what result does this formula return:

=COUNTIF($AB$4:$AB$37448,">="&Q4)
 

Alex O

Active Member
Joined
Mar 16, 2009
Messages
343
Office Version
  1. 365
Platform
  1. Windows
I think I found a winner:
=IF(Q5="",SUMIF($AC$4:$AC$37448,I5,$AE$4:$AE$37448),SUMIFS(D4:D6813,B4:B6813,"="&I5,C4:C6813,">="&Q5))

Thanks for all of the direction and input!
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
It returns 0...
Ok, then that means 0 is a valid result.

The COUNTIF formula was just another way of checking this expression:

...($AB$4:$AB$37448>=Q4)...

If the result is still not correct I'd need to see the actual file to figure out what the problem is. However, I don't like looking at other peoples files if they contain 10's of 1000's of rows of data!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,328
Members
414,054
Latest member
Sameer50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top