Forum Discussion

Mark_1008's avatar
Mark_1008
Copper Contributor
Sep 26, 2026

Looking for Excel Function Advise

I need some guidance on what function to use to sum of a data range for criteria contained in another data range.

 

Down in Cell E78, I would like to count the number of adults attending a wedding (range $E5:$E68) IF Dn in range $D5:$D68 = "Y".

The Countif or countifs does not seem to be quite the right function.

Please provide any ideas you may have.  Thank you!

 

 

Formulas and Functions

2 Replies

  • IlirU's avatar
    IlirU
    Iron Contributor
    Oct 01, 2026

    Hi Mark_1008​,

    Try this formula in cell E78:

    =BYROW(TRANSPOSE((D5:D68 = TRANSPOSE(SUBSTITUTE(TEXTAFTER(B78:B81, "("), ")", ""))) * E5:E68), SUM)

    HTH

    IlirU

  • mathetes's avatar
    mathetes
    Gold Contributor
    Sep 28, 2026

    You're right that COUNTIF isn't the right function. I want to encourage you to learn to play around, guess around, use your intuition a bit. In this case, you want to add things up, right, not just count whether there's an entry in a column. Add things up, hmmm. Turns out that there's a SUMIF function, which works much like COUNTIF but adds up entries in one column based on criteria met in another.

    Do  you have access to any Excel resources? If not, here's a link to the SUMIF function as described in ExcelJet.Net, a resource that I recommend.