Additional condition for sum product

Hi

I managed to get the formula below working:
=SUM(SUMIF(INDIRECT("'"&D4&"'!"&"$A$6:$A$54"),E4:X4,INDIRECT("'"&D4&"'!"&"$C$6:$D$54")))

However I missed a requirement. Currently it looks within a range on the summary sheet EX:X4. Most of these have two digits e.g. "JA", ","JB","JD" etc which the formula works for.

BUT, also within this range are single digits e.g. "H" - what I would like a formula to do is sum all the instances on the sheet to be summed which start with the Letter e.g. in this example of "H" on the summary sheet I would need it to sum "HA", "HB" and "HC" on the sheet APP024

Can anyone help?

Try it like this in AA4:

=SUM(SUMIF(INDIRECT("'"&D4&"'!"&"$A$6:$A$54"),E4:X4&IF(E4:X4="H","*",""),INDIRECT("'"&D4&"'!"&"$C$6:$D$54")))

(new bits added in red). Use CSE to commit the formula, then copy down.

Hope this helps.

Pete

Many thanks Pete for this - it works.

I need to be annoying and change the requirement slightly. In the formula you provided it currently looks for the creiteria "H" only.

Is there any way in which it could look up other single and apply the same logic e.g. if there was a cell with just "A" then I would need it to look up all instances of A*

In my data I have single instances of A/H/K/X but there could be more. Would I need to add each condition or could a formula exist which would look up the length of the cell then if 1 it would then use the cell and "*"?

Hope that makes sense?

Okay, slight amendment, shown in red:

=SUM(SUMIF(INDIRECT("'"&D4&"'!"&"$A$6:$A$54"),E4:X4&IF(LEN(E4:X4)=1,"*",""),INDIRECT("'"&D4&"'!"&"$C$6:$D$54")))

Hope this helps.

Pete

It does - Thank you!!

Works brilliantly

Glad to help - thanks for the rep.

Pete