Consolidation of partners' 4W reports and calculation of people reached without double counting

• (a) K2: =IF(SUM(F2:I2)=J2,"OK","Difference "&SUM(F2:I2)-J2)
• (b) L2: =A2&"|"&C2&"|"&D2&"|"&E2 (after normalising the month); M2: =IF(COUNTIF($L$2:L2,L2)>1,"Duplicate",""). The expanding range ($L$2:L2) marks only the second occurrence and keeps the first.
• (c) N2: =XLOOKUP(C2,Sites!$A$2:$A$6,Sites!$C$2:$C$6,"Unknown site") or =IFERROR(INDEX(Sites!$C$2:$C$6,MATCH(C2,Sites!$A$2:$A$6,0)),"Unknown site"). The official district is retrieved in the same way (column B of "Sites") instead of relying on the district entered in column B.
• (d) B2 of the summary: =MAXIFS(Data!$O$2:$O$13,Data!$C$2:$C$13,A2,Data!$E$2:$E$13,"2026-10",Data!$M$2:$M$13,"<>Duplicate"), where O is a recalculated total (=SUM(F2:I2)) and not the entered total J, which may be wrong.
• (e) C2: =IF(B2>XLOOKUP(A2,Sites!$A$2:$A$6,Sites!$D$2:$D$6),"To be checked","")

The month criterion only works if column E is normalised first (see question 2).

Improvements: (1) drop-down lists (data validation) for partner, district, site and month, fed by the "Sites" sheet; (2) total calculated automatically and locked; (3) conditional formatting that highlights duplicates and figures above the population; (4) a deadline and written feedback to each partner on their anomalies.

Achievement rate: Baidoa 3,400 / 5,000 = 68.0%; Burhakaba 1,440 / 2,000 = 72.0%; total 4,840 / 7,000 = 69.1%. Children account for 48.8% of people reached (2,360 / 4,840) and women and girls for 58.1% (2,810 / 4,840).

Simple sum of the 10 valid rows (excluding rows 9 and 13, row 10 at 700): 8,870 (Baidoa 6,730; Burhakaba 2,140), i.e. 126.7% of the target. It counts the same people several times, as they receive a kit, water and a hygiene promotion session at the same site. It is useful for counting beneficiaries per activity, not people. Barwaaqo remains flagged (1,200 for an estimated 1,100 inhabitants) pending verification.

The maximum rule avoids adding the same people several times: in Raamo, the estimated population is 1,750; adding 1,200 + 1,050 + 1,600 = 3,850 would give an impossible figure. The partner's work does not disappear: it remains visible in the number of beneficiaries per activity (table by activity and by partner), which is published alongside the number of people reached. Limit: the maximum is a low estimate if the activities partly reach different people; the true value lies between the maximum (1,600) and the sum, capped by the population (1,750). For a more accurate calculation: unique household identifiers shared between partners (common list or registration database), with a data sharing agreement; or a coverage survey on the sites. The method and its limits are stated in a footnote to each product.

I do not publish 8,870 as the number of people reached: it is wrong (126.7% of the target, and more than the population of some sites) and contrary to the method agreed by the sector. An inflated figure would be spotted by donors or by another sector, and would cast doubt on all our data; it would also distort the analysis of unmet needs. I propose an honest presentation that still shows the work done: 4,840 people reached (69.1% of the target), 8,870 services delivered by activity, a map by site and an explanation of the gaps (Barwaaqo being verified). If the request is maintained, I document it and raise it with my supervisor or the information management lead.