can anyone simplify this equation please?

[englishdad]

I'm thinking it could be done using a sumifs function but I can't get past the thinking stage! Any help, as always, is very much appreciated. Thanks in advance

SUMIF(C7:C41,"PAID",D7:D41)+SUMIF(F7:F41,"PAID",G7:G42)+SUMIF(I7:I41,"PAID",J7:J41)+SUMIF(L7:L41,"PAID",M7:M41)+SUMIF(O7:O41,"PAID",P7:P42)+SUMIF(R7:R41,"PAID",S7:S41)+SUMIF(U7:U41,"PAID",V7:V42)+SUMIF(X7:X41,"PAID",Y7:Y41)+SUMIF(AA7:AA41,"PAID",AB7:AB41)+SUMIF(AD7:AD41,"PAID",AE7:AE41)+SUMIF(AG7:AG41,"PAID",AH7:AH41)+SUMIF(AJ7:AJ41,"PAID",AK7:AK41)+SUMIF(AM7:AM41,"PAID",AN7:AN41)+SUMIF(AP7:AP41,"PAID",AQ7:AQ41)+SUMIF(AS7:AS41,"PAID",AT7:AT41)+SUMIF(AV7:AV41,"PAID",AW7:AW41)

 

[englishdad]

Thanks Aladin, just tried yours too, a variation on the above. It's all a bit hieroglyphic to me at the moment, but thanks a lot for helping! Any ideas on my other puzzle?

 

[Aladin Akyurek]

Thanks Aladin, just tried yours too, a variation on the above. It's all a bit hieroglyphic to me at the moment, but thanks a lot for helping!

What I posted is not a variation, rather a correction to the long existing non-robust originals.

Any ideas on my other puzzle?

Care to word the puzzle in words if the formulas you posted do not resolve it already?

 

[englishdad]

Fair Enough Aladin, my thanks to you as always

The puzzle in words...Hmmm...

Your array function (and the one from njimack above) sums certain cells if those cells contain 'PAID'

I would like to sum those cells that are within certain date ranges as mentioned above. I have a column of invoice numbers (ie B7:B41), a column of due dates (ie C7:C41) and a column of invoice values (ie D7:D41). This repeats, EFG, HIJ etc until AX,AY,AZ.

Hope that helps

 

[Aladin Akyurek]

Given:

1,7/11/2017,15,1,7/18/2017,45

What is the expected result?

Note. Try to insert a column in front of the C column, do you still have a correct result with the earlier formula?

 

[englishdad]

Hi Aladin

I know I'm being thick, but I'm not entirely sure what you mean??

 

[Aladin Akyurek]

Hi Aladin

I know I'm being thick, but I'm not entirely sure what you mean??

I posted a small heap of data and asked for the result that would obtain for this data according to your specs...

 

[englishdad]

Just to clarify and approach from a different angle, I took your function...=SUM(IF(MOD(COLUMN(D7:AW41)-COLUMN(D7),3)=0,IF(C7:AV41="paid",D7:AW41)))
and replaced the ="PAID" with >TODAY(), expecting that it would sum all cells that have a date in the future. The resultant sum was 462269.69. To check, I then manually highlighted all the cells that had a future date to get a value of 212256.13. Clearly I'm missing something here, or perhaps your function cannot be altered??

If you have any spare time to have a look, I have uploaded the sheet to dropbox here ... https://www.dropbox.com/s/tik1u0o8kuiggtt/Book1.xlsx?dl=0

Thanks

 

[Aladin Akyurek]

Since C7:AV41 houses text like PIAD and text > number is true, we should avoid false positives...

Control+shift+enter:

=SUM(IF(MOD(COLUMN(D7:AW41)-COLUMN(D7),3)=0,IF(ISNUMBER(C7:AV41),IF(C7:AV41>TODAY(),D7:AW41))))

 

[Aladin Akyurek]

And X2 in your workbook:

{=SUM(IF(MOD(COLUMN(D7:AZ41)-COLUMN(D7),3)=0,D7:AZ41))}

X3:

{=SUM(IF(MOD(COLUMN(D7:AW41)-COLUMN(D7),3)=0,IF(C7:AV41="paid",D7:AW41)))}

AF5:

{=SUM(IF(MOD(COLUMN(D7:AW41)-COLUMN(D7),3)=0,IF(ISNUMBER(C7:AV41),IF(C7:AV41<=TODAY()-7,D7:AW41))))}

 

[englishdad]

Hi Aladin

Many thanks, I've replaced the function above in post #18 and changed today() with today()+7 because this represents all current invoices that are more than 7 days away from being due. This function now goes in cell K5. I am now left with cells L5, N5, O5 & Q5.

I then put the function {=SUM(IF(MOD(COLUMN(D7:AW41)-COLUMN(D7),3)=0,IF(ISNUMBER(C7:AV41),IF(C7:AV41<=TODAY()-7,D7:AW41))))} in cell Q5 (changing today()-7 to today-15) and that seems to work.

This leaves me with green, amber & pink, L5, N5 & O5 and that is where I am stuck because they are not just over a certain number or under a certain number (ie grey & red, K5/Q5), they are between numbers and I'm not sure how to do that?????

Many thanks again for your help