Only need first instance of SUMIF in a column

[Lizabeta]

I have many kitchens using the same recipes. I need to distill information down until I've got a summary of how much is being made.
Uploaded is a condensed version of the point in the process I'm having difficulty with.

This workbook will pull information from 8 other workbooks and give me excatly what everyone made on any weekday.

And from there, with the kind help of this forum, I figured out how to do a SUMIF based on the recipe number. And it summed up all instances of 'Recipe X' being used.

However, it continues to SUMIF itself all the way down the page... which is good, because of how recipes are chosen for each kitchen. However, I only need to report one instance of each recipe.

In the uploaded example (and I apologize for the colorful sheet, but it helped me double check what I was working on.) ... I only need to report the PURPLE results elsewhere... the first instance of each SUMIF.

(I have a terrible feeling this is going to require VBA at which I'm horribly inept)

[daddylonglegs]

Hello Lizabeta,

Try changing F5 to this formula copied down

=IF(COUNTIF(A$5:A5,A5)=1,SUMIF(A$5:A$18,A5,$D$5:D$18),"")

[Lizabeta]

Bless all of your eight long legs! It Worked! It's beautiful!

Can you translate that into what it is doing/referring to? I like to be able to apply these forum lessons into other uses too!

Thank you so much!

Good grief: I can't figure out how to mark it solved. I blame the post 5 o clock hour.

[daddylonglegs]

The countif function counts how many instances of that row's A column value exist in that row and higher. Of course there will be at least 1 (in that row). The formula executes the SUMIF only when the COUNTIF returns 1, i.e. there are no previous duplicate values for that row's column A value.

I edited the thread to "Solved"

[Lizabeta]

=IF(COUNTIF(A$5:A5,A5)=1,SUMIF(A$5:A$18,A5,$D$5:D$18),"")

So..

IF (Countif finds the Value of A5 once) then SUM (all recipes amounts that match the recipe number in A5), if A5 has no recipe number or it finds more than =1 of the instances- report nothing

[daddylonglegs]

That's right.

You have to bear in mind that the COUNTIF will only look at the current row and higher, so by row 10 for instance the formula will be:

=IF(COUNTIF(A$5:A10,A10)=1,SUMIF(A$5:A$18,A10,$D$5:D$18),"")

The countif can't return zero, it must be at least 1 because the value to count, A10, is included in the range to count, A5:A10. If the COUNTIF returns 2 or higher that means that you already have a sum for that recipe on a previous row, so the formula returns a blank, ""