Query function to distinguish a column to different column

I won't follow your link, but going from the screenshot you can try this formula in J3:

=SUMIFS(E:E,A:A,G3)

Copy down as required.

Hope this helps.

Pete

Originally Posted by Pete_UK

I won't follow your link, but going from the screenshot you can try this formula in J3:

=SUMIFS(E:E,A:A,G3)

Copy down as required.

Hope this helps.

Pete

Thank you. But not of help at all
I would like to have the whole table using only one formula


From G2 to J4 I am using a query formula

Okay, thanks for the feedback - good luck with that.

Pete

Also; if you want to produce the whole table in range G2:L3 with only a single formula, clear the cells G2:L3 in your sheet and enter this formula in cell G2 :

Please Login or Register  to view this content.

the {curly brackets} treats the 3 queries as an array, otherwise the formula will not work... you did say in opening post that it must a single formula, so this is necessary.

also note that when using this particular formula, for each invoice there must be a "Fee" AND at least one type of "Outlay". if the source data only has a Fee OR an Outlay then the result will be an #REF! error.

As mentioned by janmorris in his message; you need to have at least 1 data either blank or zero for a FEE and OUTLAY item, although it may not exist in reality.... as seen below;