Modify the equation to bring in the dollar data first.

[Hany ali]

Hello My Masters ,please Help me to Modify the equation to bring in the dollar data first. then egyption Pound after that

=IFERROR(INDEX(Sheet1!$A$2:$A$45,AGGREGATE(15,6,ROW($A$1:$A$310)/(MATCH(Sheet1!$A$2:$A$45&Sheet1!$A$2:$A$45,Sheet1!$A$2:$A$45&Sheet1!$A$2:$A$45,0)=ROW($A$1:$A$310)),ROWS($2:2))),"")

which in column A from Total Sheet to Get Date From Column A in Sheet1 Sheet Based on the available amounts in columns F & G ,To Get supplier name first For USD Curr. and After That For Egyption Pound
thanks with my Best Regards

 

[p45cal]

In the attached, on Sheet Total, formula in cell A3 only, spills down.
Hints as to what belongs where:

Usual SUMIFS formula in cells B28:B29. You may have to move these down the sheet if you have more source data.

You'll note that I've used structured table references (eg. Table1[Supplier Name]) because I converted your source data into a proper Excel table. I did this for 2 reasons:
1. You won't have to change the formula when the amount/content of data in the source table changes
2. I used Power Query to create the data for a pivot table at cell N2 of the Result sheet for cross-checking my results.

[You don't need the Power Query or your source data converted to a proper Excel table or the Pivot table although it's my preference as it's easy, quick and gets the results you need flexibly.]

You don't have to convert your source data into a proper Excel table, and to show this I've used the same formula with the more usual range references in cell F3 of Result sheet:
=SummaryUsdEgp(Sheet1!A2:A45,Sheet1!F2:F45,Sheet1!G2:G45)

For interest only, in cell J3 of the Result sheet is the full worksheet version of the SummaryUsdEgp named lambda formula which you can find in Name Manager.

I've rearranged the columns on the Total sheet so that the Cheque Name column is outside the 3 columns produced by the formula.

 

[Peter Bartholomew]

Good luck with that! It gets fairly complicated even with a modern version of Excel.

= LET(
    supplyUSD, UNIQUE(FILTER(Supplier_Name, Amount>0)),
    supplyEGP, UNIQUE(FILTER(Supplier_Name, Amount_L.E>0)),
    USD,       SUMIFS(Amount,     Supplier_Name, supplyUSD),
    EGP,       SUMIFS(Amount_L.E, Supplier_Name, supplyEGP),
    VSTACK(
        HSTACK(supplyUSD, EXPAND(USD,,2, "USD")),
        HSTACK(supplyEGP, EXPAND(EGP,,2, "EGP"))
    )
  )

Note: I have only just seen @p45cal 's post. I like both the use of an Excel Table for source data and of using a Lambda function to hold the formula.

 

[Hany ali]

p45cal thanks Alot My Dear, it's very good Function .But I Haven't 365 ,,if Possible Normal Function ?

Peter Bartholomew thanks alot my Dear If Possible By Normal Function ?​

I can't handle it by power query

​

​

 

[p45cal]

Of the following worksheet functions, state which are available to you and which are not and I'll try to rewrite:
LET
LAMBDA
UNIQUE
FILTER
EXPAND
HSTACK
SUMIFS
VSTACK
You can check by typing into a cell:
= followed by the first few characters of each of the functions, one at a time and a list of functions should appear. If you have that function available it should appear in that list.
An examination of the file you attached suggests that many, if not all, are available to you.
What version of Excel do you have? I think you only need 2021 or above.

Separately,

I can't handle it by power query

Why not? You certainly have it.

 

[Peter Bartholomew]

Sorry. Count me out. I only program for 365 and do not work with earlier versions of Excel.
If your version of Excel is 2010 or later then you should be able to work with Power Query.

 

[Hany ali]

thanks

p45cal all these Function Not Work with Me except SUMIFS ?​

Sorry Mr.

Peter Bartholomew I Didn't Work By Power Query Yet​

 

[p45cal]

@p45cal all these Function Not Work with Me except SUMIFS ?

Then Power Query is the way to go. I'm not going to re-write what is already a complicated formula into old versions of Excel. These formulae would be totally unmanageable.
In the attached a Pivot Table at cell F2 of the Total sheet based on your source data transformed by Power Query.

Separately, without Power Query, 2 pivot tables can be created directly from your source data, one for each currency; see cells J2 & M2 of the Total sheet.

 

[Hany ali]

thanks my Dear
Nice Way ..Sorry , But I Can't Understand it

 

[p45cal]

I have a 4 year old car. I need to travel 2,000 miles. I'm going to cycle.

 

[Hany ali]

up Please , If Possible Normal Function