Is this formula flawed

Alex O

Active Member
Joined
Mar 16, 2009
Messages
345
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>
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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),....
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0
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)
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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
Back
Top